What are TRIGGERS in DB2? What is the difference between TRIGGERS and STORED PROCEDURES?

The TRIGGERS are database programs which are triggered automatically by DBMS in response to any modification done on the specified table. A TRIGGER can be associated with only a single table and they cannot be skipped if the desired event occurs.

The TRIGGERS are like STORED PROCEDURES in the sense, both are pieces of code which are directly managed by DB2. However, TRIGGERS are event driven and executed automatically once the desired event occurs (INSERT/UPDATE/DELETE) on the specified table.

The STORED PROCEDURES on the other hand needs to be called explicitly in the application program. Another difference between them is, TRIGGERS are associated with only a single table but in case of STORED PROCEDURES we can use multiple tables.

The below blocks illustrate the functioning of STORED PROCEDURES and TRIGGERS.

The following are three important points which highlight the difference between TRIGGERS and STORED PROCEDURES.

  • The STORED PROCEDURE returns the result or data from the database but TRIGGER only makes the changes in the database and does not return any data.
  • The TRIGGERS are event driven, i.e., it activates whenever any changes are made in the database. However, the STORED PROCEDURES are not event driven.
  • The TRIGGER can operate only on a single table (INSERT/UPDATE/DELETE); on the other hand the STORED PROCEDURE can operate on a multiple table.