This appendix describes the interaction of triggers with referential constraints and check constraints that may result from an update operation. Figure 18 and the associated description are representative of the processing that is performed for an SQL statement that updates data in the database.
Figure 18. Processing an SQL statement with associated triggers and constraints
Figure 18 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 18.
This is the DELETE, INSERT, or UPDATE statement that begins the process. The SQL statement S1 identifies a table (or an updatable view over some table) referred to as the target table throughout this description.
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:
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.
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 S1 (so far) are rolled back.
If there are no BEFORE triggers or the SAR is empty, this step is skipped.
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 S1 (so far) are rolled back.
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 S1 (so far) are rolled back.
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 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 S1 (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 S1 and performing all of the steps described here recursively.
Once all triggered SQL statements from all AFTER triggers activated by each S1 have been processed to completion, the processing of the original S1 is completed.
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 S1. The database is therefore back in the same state as immediately prior to the execution of the original SQL statement S1