SQL Reference

Appendix J. Interaction of Triggers and Constraints

This appendix describes the interaction of triggers with referential constraints and check constraints that may result from an update operation. Figure 17 and the associated description are representative of the processing that is performed for an SQL statement that updates data in the database.

Figure 17. Processing an SQL statement with associated triggers and constraints

Figure 17 shows the general order of processing for an SQL 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 17.

• SQL statement S₁

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

• Determine set of affected rows (SAR)

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

  The purpose of this step is to determine the set of affected rows for the SQL statement. The set of rows included in SAR 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 SAR is empty, there will be no BEFORE triggers, changes to apply to the target table, or constraints to process for the SQL 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 SAR.

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

  If there are no BEFORE triggers or the SAR is empty, this step is skipped.

• Apply SAR to the target table

  The actual delete, insert, or update is applied using SAR to the target table in the database.

  An error may occur when applying SAR (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 SQL statement S₁ (so far) are rolled back.

• Apply Constraints

  The constraints associated with the target table are applied if SAR 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 may 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 S₁ (so far) are rolled back.

• Process AFTER triggers

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

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

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

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

  A cascaded SQL 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 SQL statement as a new S₁ and performing all of the steps described here recursively.

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

• (R) = roll back changes to before S₁

  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 SQL statement S₁. The database is therefore back in the same state as immediately prior to the execution of the original SQL statement S₁