Examples of interaction between triggers and referential constraints

Update operations can cause the interaction of triggers with referential constraints and check constraints.

Figure 1 and the associated description are representative of the processing that is performed for an statement that updates data in the database.

Figure 1. Processing an statement with associated triggers and constraints
Processing an statement with associated triggers and constraints
Figure 1 shows the general order of processing for an statement that updates a table. It assumes a situation where the table includes BEFORE triggers, referential constraints, check constraints and AFTER triggers that cascade. The following is a description of the boxes and other items found in Figure 1.
  • statement S1

    This is the DELETE, INSERT, or UPDATE statement that begins the process. The statement S1 identifies a table (or an updatable view over some table) referred to as the subject table throughout this description.

  • Determine set of affected rows

    This step is the starting point for a process that repeats for referential constraint delete rules of CASCADE and SET NULL and for cascaded statements from AFTER triggers.

    The purpose of this step is to determine the set of affected rows for the statement. The set of rows included is based on the statement:
    • for DELETE, all rows that satisfy the search condition of the statement (or the current row for a positioned DELETE)
    • for INSERT, the rows identified by the VALUES clause or the fullselect
    • for UPDATE, all rows that satisfy the search condition (or the current row for a positioned UPDATE).

    If the set of affected rows is empty, there will be no BEFORE triggers, changes to apply to the subject table, or constraints to process for the statement.

  • Process BEFORE triggers

    All BEFORE triggers are processed in ascending order of creation. Each BEFORE trigger will process the triggered action once for each row in the set of affected rows.

    An error can occur during the processing of a triggered action in which case all changes made as a result of the original statement S1 (so far) are rolled back.

    If there are no BEFORE triggers or the set of affected is empty, this step is skipped.

  • Apply the set of affected rows to the subject table

    The actual delete, insert, or update is applied using the set of affected rows to the subject table in the database.

    An error can occur when applying the set of affected rows (such as attempting to insert a row with a duplicate key where a unique index exists) in which case all changes made as a result of the original statement S1 (so far) are rolled back.

  • Apply Constraints

    The constraints associated with the subject table are applied if set of affected rows is not empty. This includes unique constraints, unique indexes, referential constraints, check constraints and checks related to the WITH CHECK OPTION on views. Referential constraints with delete rules of cascade or set null might cause additional triggers to be activated.

    A violation of any constraint or WITH CHECK OPTION results in an error and all changes made as a result of S1 (so far) are rolled back.

  • Process AFTER triggers

    All AFTER triggers activated by S1 are processed in ascending order of creation.

    FOR EACH STATEMENT triggers will process the triggered action exactly once, even if the set of affected rows is empty. FOR EACH ROW triggers will process the triggered action once for each row in the set of affected rows.

    An error can occur during the processing of a triggered action in which case all changes made as a result of the original S1 (so far) are rolled back.

    The triggered action of a trigger can include triggered statements that are DELETE, INSERT or UPDATE statements. For the purposes of this description, each such statement is considered a cascaded statement.

    A cascaded statement is a DELETE, INSERT, or UPDATE statement that is processed as part of the triggered action of an AFTER trigger. This statement starts a cascaded level of trigger processing. This can be thought of as assigning the triggered statement as a new S1 and performing all of the steps described here recursively.

    Once all triggered statements from all AFTER triggers activated by each S1 have been processed to completion, the processing of the original S1 is completed.

  • R = roll back changes to before S1

    Any error (including constraint violations) that occurs during processing results in a roll back of all the changes made directly or indirectly as a result of the original statement S1. The database is therefore back in the same state as immediately before the execution of the original statement S1