The BEFORE | AFTER clause specifies whether to execute the trigger before or after the invoking DML statement. In some circumstances, the BEFORE and AFTER clauses are interchangeable. However, there are some situations where one clause is preferred over the other.
▪It is more efficient to use the BEFORE clause when performing data validation, such as domain constraint and referential integrity checking.
▪When you use the AFTER clause, table rows which become available due to the invoking DML statement are processed. Conversely, the AFTER clause also confirms data deletion after the invoking DELETE statement.
You can define up to six triggers per table, one for each combination of action (INSERT, UPDATE, DELETE) and time (BEFORE and AFTER):
▪BEFORE INSERT
▪BEFORE UPDATE
▪BEFORE DELETE
▪AFTER INSERT
▪AFTER UPDATE
▪AFTER DELETE
The following example shows trigger trig01 defined BEFORE INSERT ON table1.
"CREATE TRIGGER TRIG01 ON table1 BEFORE INSERT REFERENCING NEW COL1 AS NEW_COL1 BEGIN EXEC SQL PREPARE CUR1 INSERT INTO T2 VALUES (?); EXEC SQL EXECUTE CUR1 USING (NEW_COL1); EXEC SQL CLOSE CUR1; EXEC SQL DROP CUR1; END"
Following are examples (including implications and advantages) of using the BEFORE and AFTER clause of the CREATE TRIGGER command for each DML operation:
▪UPDATE operation
The BEFORE clause can verify that modified data follows integrity constraint rules before processing the UPDATE. If the REFERENCING NEW AS new_column_identifier clause is used with the BEFORE UPDATE clause, then the updated values are available to the triggered SQL statements. In the trigger, you can set the default column values or derived column values before performing an UPDATE.
The AFTER clause can perform operations on newly modified data. For example, after a branch address update, the sales for the branch can be computed.
If the REFERENCING OLD AS old_column_identifier clause is used with the AFTER UPDATE clause, then the values that existed prior to the invoking update are accessible to the triggered SQL statements.
▪INSERT operation
The BEFORE clause can verify that new data follows integrity constraint rules before performing an INSERT. Column values passed as parameters are visible to the triggered SQL statements but the inserted rows are not. In the trigger, you can set default column values or derived column values before performing an INSERT.
The AFTER clause can perform operations on newly inserted data. For example, after insertion of a sales order, the total order can be computed to see if a customer is eligible for a discount.
Column values are passed as parameters and inserted rows are visible to the triggered SQL statements.
▪DELETE operation
The BEFORE clause can perform operations on data about to be deleted. Column values passed as parameters and inserted rows that are about to be deleted are visible to the triggered SQL statements.
The AFTER clause can be used to confirm the deletion of data. Column values passed as parameters are visible to the triggered SQL statements. The deleted rows are visible to the triggering SQL statement.