Interactions between triggers and referential constraints

When you create triggers, you need to understand the interactions among the triggers and constraints on your tables. You also need to understand the effect that the order of processing of those constraints and triggers can have on the results.

In general, the following steps occur when triggering SQL statement S1 performs an insert, update, or delete operation on table T1:
  1. DB2® determines the rows of T1 to modify. Call that set of rows M1. The contents of M1 depend on the SQL operation:
    • For a delete operation, all rows that satisfy the search condition of the statement for a searched delete operation, or the current row for a positioned delete operation
    • For an insert operation, the row identified by the VALUES statement, or the rows identified by the result table of a SELECT clause within the INSERT statement
    • For an update operation, all rows that satisfy the search condition of the statement for a searched update operation, or the current row for a positioned update operation
  2. DB2 processes all before triggers that are defined on T1, in order of creation.

    Each before trigger executes the triggered action once for each row in M1. If M1 is empty, the triggered action does not execute.

    If an error occurs when the triggered action executes, DB2 rolls back all changes that are made by S1.

  3. DB2 makes the changes that are specified in statement S1 to table T1, unless an INSTEAD OF trigger is defined for that action. If an appropriate INSTEAD OF trigger is defined, DB2 executes the trigger instead of the statement and skips the remaining steps in this list.

    If an error occurs, DB2 rolls back all changes that are made by S1.

  4. If M1 is not empty, DB2 applies all the following constraints and checks that are defined on table T1:
    • Referential constraints
    • Check constraints
    • Checks that are due to updates of the table through views defined WITH CHECK OPTION

    Application of referential constraints with rules of DELETE CASCADE or DELETE SET NULL are activated before delete triggers or before update triggers on the dependent tables.

    If any constraint is violated, DB2 rolls back all changes that are made by constraint actions or by statement S1.

  5. DB2 processes all after triggers that are defined on T1, and all after triggers on tables that are modified as the result of referential constraint actions, in order of creation.

    Each after row trigger executes the triggered action once for each row in M1. If M1 is empty, the triggered action does not execute.

    Each after statement trigger executes the triggered action once for each execution of S1, even if M1 is empty.

If any triggered actions contain SQL insert, update, or delete operations, DB2 repeats steps 1 through 5 for each operation.

If an error occurs when the triggered action executes, or if a triggered action is at the 17th level of trigger cascading, DB2 rolls back all changes that are made in step 5 and all previous steps.

For example, table DEPT is a parent table of EMP, with these conditions:
  • The DEPTNO column of DEPT is the primary key.
  • The WORKDEPT column of EMP is the foreign key.
  • The constraint is ON DELETE SET NULL.
Suppose the following trigger is defined on EMP: 
CREATE TRIGGER EMPRAISE
  AFTER UPDATE ON EMP
  REFERENCING NEW TABLE AS NEWEMPS
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
    VALUES(CHECKEMP(TABLE NEWEMPS));
  END
Also suppose that an SQL statement deletes the row with department number E21 from DEPT. Because of the constraint, DB2 finds the rows in EMP with a WORKDEPT value of E21 and sets WORKDEPT in those rows to null. This is equivalent to an update operation on EMP, which has update trigger EMPRAISE. Therefore, because EMPRAISE is an after trigger, EMPRAISE is activated after the constraint action sets WORKDEPT values to null.