1.1.1

CREATE TRIGGER
A trigger defines a set of actions that are executed when a delete, insert, or update operation is performed on a
table. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever
someone deletes a row or rows from the table.
Syntax
CREATE TRIGGER trigger-name
{ AFTER | NO CASCADE BEFORE }
{ INSERT | DELETE | UPDATE [ OF column-name [, column-name]* ] }
ON table-name
[ ReferencingClause ]
[ 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.
507
SQL Language Reference