1.0

[ FOR EACH ROW ] [ MODE DB2SQL ]
triggered-sql-statement
Description
Note: Statement triggers are not supported. Trigger recursion is not supported.
In addition to providing constraints, triggers can help enforce data integrity rules with actions such as deletes or
updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending
e-mail, and other useful actions.
You can define any number of triggers for a single table, including multiple triggers on the same table for the
same event. When multiple triggers are defined for the same database event for the same table for the same
trigger time (before or after), triggers are fired in the order in which they were created.
You can create a trigger in any schema where you are the schema owner. To create a trigger on a table that you
do not own, you must be granted the TRIGGER privilege on that table. The database owner can also create
triggers on any table in any schema.
The trigger does not need to reside in the same schema as the table on which the trigger is defined.
If a qualified trigger name is specified, the schema name cannot begin with SYS.
Before and After Triggers
Triggers can be defined as either Before or After triggers using the NO CASCADE BEFORE or AFTER clauses.
Before triggers fire before the statement's changes are applied, and before any constraints have been applied.
Before triggers can be either row or statement triggers. After triggers fire after all constraints have been satisfied
and after the changes have been applied to the target table.
Trigger Events
You can define a trigger to occur in response to any of these DML events:
• INSERT
• UPDATE
• DELETE
You can define any number of triggers for a given event on a given table. With update events, you can specify
columns.
ReferencingClause
The triggered-sql-statement may need to reference data that is being changed by the database event that fired
the trigger. The triggered-sql-statement may also need to refer to the new (post-change or "after") values.
Changed data can be referenced in the triggered-sql-statement using transition variables. The referencing clause
enables you to provide a correlation name or alias for these transition variables by specifying REFERENCING
OLD AS correlation-name or REFERENCING NEW AS correlation-name.
For example, if you add the following clause to the trigger definition:
REFERENCING OLD AS DELETEDROW
You can then refer to the correlation name in the triggered-sql-statement:
DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id
The OLD and NEW transition variables map to a java.sql.ResultSet with a single row.
465
SQL Language Reference