Products Support Documentation Download
SQL Trigger Specification

A trigger is defined through the execution of the SQL create trigger statement. The syntax for the create trigger statement is shown below.

trigger_def : create trigger [database_name.]trigger_name
{before | after} trigger_event
on [database_name.]table_name
[referencing transition...]
[for each {row [when (conditional_expr)] | statement}] trigger_stmts
trigger_event : insert
| delete
| update [of column_name [, column_name]...]
transition : old [row] [as] old_row_correlation_name
| new [row] [as] new_ row_correlation_name
trigger_stmts : sql_stmt
| begin atomic {sql_stmt;}... end
sql_stmt : a SQL PL statement

Each trigger has a unique name, trigger_name, which conforms to a standard SQL identifier. Triggers are associated with a table in a database. The name of the database can qualify either the trigger_name or the table_name. If not specified, the trigger will be associated with the most recently opened database that contains table_name.

The table_name is the name of the base table with which the trigger is to be associated. The trigger will be fired either before or after the changes are made by the trigger_event which is either an insert, update or delete statement.

The referencing clause allows correlation names to be given to the row values as they exist prior to the data modifications (old row) or to the row values that exist after the data modifications (new row). If the trigger_event is an insert then only new can be specified. If the trigger_event is a delete then only old can be specified. If the trigger_event is an update then either or both old and new can be specified. Column or row values can then be accessed through these correlation names.

The for each clause specifies whether the trigger is fired only once whenever the trigger_event statement is executed (for each statement) or if the trigger is to be fired for each row that is changed by the trigger_event (for each row). The default is for each statement. If old or new row is specified then for each row must be specified as well.

The when clause can be defined in a row-level trigger to specify a condition (which can reference any of the old or new values through the referencing correlation names) that must evaluate to true for the trigger to fire.

The SQL statement to be executed when the trigger fires is specified last. If more than one statement needs to be specified, it must be placed within a begin atomic and end block. This means that either all the SQL statements succeed or if any one fails then the state is restored to its pre-trigger execution condition.

There are some restrictions on the kinds of SQL statements that can be included in a trigger. No select, DDL, or create statements are allowed in a trigger. A trigger cannot create another trigger. A stored procedure cannot create a trigger. Also, since it is necessary that any database modifications made by a trigger be included as part of the user's transaction, no transaction statements are allowed in a trigger definition. Parameter markers cannot be specified in trigger definitions.