CP363 : SQL Triggers

Triggers are used whenever referential integrity and other declarative constraints are not sufficient.

You may want to enforce a more complex form of referential integrity involving more detailed checking, or you may want to enforce checking on new data but allow legacy data to violate constraints. Another use for triggers is in logging the activity on database tables, independent of the applications using the database.

Trigger execution permissions-Triggers execute with the permissions of the owner of the associated table, not the user ID whose actions cause the trigger to fire. A trigger can modify rows in a table that a user could not modify directly.

Triggers can be defined on one or more of the following triggering actions:

Action Description
INSERT The trigger is invoked whenever a new row is inserted into the table associated with the trigger
DELETE The trigger is invoked whenever a row of the associated table is deleted.
UPDATE The trigger is invoked whenever a row of the associated table is updated.
UPDATE OF column-list The trigger is invoked whenever a row of the associated table is updated such that a column in the column-list has been modified

Triggers can be defined as row-level triggers or statement-level triggers. Row-level triggers can execute BEFORE or AFTER each row modified by the triggering insert, update, or delete operation is changed. Statement-level triggers execute after the entire operation is performed.

Flexibility in trigger execution time is particularly useful for triggers that rely on referential integrity actions such as cascaded updates or deletes being carried out, or not, as they execute.

If an error occurs while a trigger is executing, the operation that fired the trigger fails. INSERT, UPDATE, and DELETE are atomic operations (see "Atomic compound statements"). When they fail, all effects of the statement (including the effects of triggers and any procedures called by triggers) are undone.

A statement-level UPDATE trigger example

The following CREATE TRIGGER statement is appropriate for statement-level UPDATE triggers:

CREATE TRIGGER mytrigger AFTER UPDATE ON employee
REFERENCING NEW AS table_after_update
            OLD AS table_before_update
FOR EACH STATEMENT
BEGIN
   ...
END

The REFERENCING NEW and REFERENCING OLD clause allows the UPDATE trigger code to refer to both the old and new values of the rows being updated. Columns in the new row are referred to with the table alias table_after_update and columns in the old row are referred to with the table alias table_before_update.

The REFERENCING NEW and REFERENCING OLD clause has a slightly different meaning for statement-level and row-level triggers. For statement-level triggers the REFERENCING OLD or NEW aliases are table aliases, while in row-level triggers they refer to the row being altered.

Syntax

CREATE TRIGGER trigger-name trigger-time trigger-event [, trigger-event,..]
... ON table-name
... [ REFERENCING [ OLD AS old-name ]
            [ NEW AS new-name ] ]
... [ FOR EACH { ROW | STATEMENT } ] MODE DB2SQL
triggered-action
... [ WHEN ( search-condition ) ]

(See Trigger Examples)