IBM Books

Application Development Guide


Ordering of Multiple Triggers

When triggers are defined using the CREATE TRIGGER statement, their creation time is registered in the database in form of a timestamp. The value of this timestamp is subsequently used to order the activation of triggers when there is more than one trigger that should be run at the same time. For example, the timestamp is used when there is more than one trigger on the same subject table with the same event and the same activation time. The timestamp is also used when there is one or more AFTER triggers that are activated by the trigger event and referential constraint actions caused directly or indirectly (that is, recursively by other referential constraints) by the triggered action. Consider the following two triggers:

    CREATE TRIGGER NEW_HIRED 
      AFTER INSERT ON EMPLOYEE 
      FOR EACH ROW MODE DB2SQL 
      BEGIN ATOMIC 
        UPDATE COMPANY_STATS 
        SET NBEMP = NBEMP + 1; 
      END; 
  
    CREATE TRIGGER NEW_HIRED_DEPT 
       AFTER INSERT ON EMPLOYEE 
       REFERENCING NEW AS EMP 
       FOR EACH ROW MODE DB2SQL 
         BEGIN ATOMIC 
           UPDATE DEPTS 
           SET NBEMP = NBEMP + 1 
           WHERE DEPT_ID = EMP.DEPT_ID; 
         END;

The above triggers are activated when you run an INSERT operation on the employee table. In this case, the timestamp of their creation defines which of the above two triggers is activated first.

The activation of the triggers is conducted in ascending order of the timestamp value. Thus, a trigger that is newly added to a database runs after all the other triggers that are previously defined.

Old triggers are activated before new triggers to ensure that new triggers can be used as incremental additions to the changes that affect the database. For example, if a triggered SQL statement of trigger T1 inserts a new row into a table T, a triggered SQL statement of trigger T2 that is run after T1 can be used to update the same row in T with specific values. By activating triggers in ascending order of creation, you can ensure that the actions of new triggers run on a database that reflects the result of the activation of all old triggers.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]