Names the trigger. The name, including the implicit or explicit qualifier, must not be the same as a trigger that already exists at the current server. QTEMP cannot be used as the trigger-name schema qualifier.
If SQL names were specified, the trigger will be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the trigger will be created in the schema that is specified by the qualifier. If not qualified, the trigger will be created in the same schema as the subject table.
If the trigger name is not a valid system name, or if a program with the same name already exists, the database manager will generate a system name. For information on the rules for generating a name, see Rules for Table Name Generation.
A DELETE trigger cannot be added to a table with a referential constraint of ON DELETE CASCADE.
An UPDATE trigger event cannot be added to a table with a referential constraint of ON DELETE SET NULL.
If an explicit column-name list is not specified, an update operation on any column of the subject table, including columns that are subsequently added with the ALTER TABLE statement, activates the triggered-action.
Each row affected by the triggering SQL operation is available to the triggered-action by qualifying columns with correlation-names specified as follows:
Only one OLD and one NEW correlation-name may be specified for a trigger. Only one OLD_TABLE and one NEW_TABLE table-identifier may be specified for a trigger. All of the correlation-names and table-identifiers must be unique from one another.
The OLD correlation-name and the OLD_TABLE table-identifier are valid only if the triggering event is either a delete operation or an update operation. For a delete operation, the OLD correlation-name captures the values of the columns in the deleted row, and the OLD_TABLE table-identifier captures the values in the set of deleted rows. For an update operation, OLD correlation-name captures the values of the columns of a row before the update operation, and the OLD_TABLE table-identifier captures the values in the set of updated rows.
The NEW ROW correlation-name and the NEW TABLE table-identifier are valid only if the triggering event is either an INSERT operation or an UPDATE operation. For both operations, the NEW ROW correlation-name captures the values of the columns in the inserted or updated row, and the NEW TABLE table-identifier captures the values in the set of inserted or updated rows. For before triggers, the values of the updated rows include the changes from any SET statements in the triggered-action of before triggers.
The OLD ROW and NEW ROW correlation-name variables cannot be modified in an AFTER trigger or INSTEAD OF trigger.
The tables below summarizes the allowable combinations of correlation variables and transition tables.
Granularity: FOR EACH ROW
MODE | Activation Time | Triggering Operation | Correlation Variables Allowed | Transition Tables Allowed |
---|---|---|---|---|
DB2ROW | BEFORE | DELETE | OLD | NONE |
INSERT | NEW | |||
UPDATE | OLD, NEW | |||
AFTER or INSTEAD OF | DELETE | OLD | ||
INSERT | NEW | |||
UPDATE | OLD, NEW | |||
DB2SQL | BEFORE | DELETE | OLD | |
INSERT | NEW | |||
UPDATE | OLD, NEW | |||
AFTER or INSTEAD OF | DELETE | OLD | OLD TABLE | |
INSERT | NEW | NEW TABLE | ||
UPDATE | OLD, NEW | OLD TABLE, NEW TABLE |
Granularity: FOR EACH STATEMENT
MODE | Activation Time | Triggering Operation | Correlation Variables Allowed | Transition Tables Allowed |
---|---|---|---|---|
DB2SQL | AFTER | DELETE | NONE | OLD TABLE |
INSERT | NEW TABLE | |||
UPDATE | OLD TABLE, NEW TABLE |
A transition variable that has a character data type inherits the CCSID of the column of the subject table. During the execution of the triggered-action, the transition variables are treated like variables. Therefore, character conversion might occur.
The temporary transition tables are read-only. They cannot be modified.
The scope of each correlation-name and each table-identifier is the entire trigger definition.
FOR EACH STATEMENT cannot be specified for a BEFORE or INSTEAD OF trigger.
FOR EACH STATEMENT cannot be specified for a MODE DB2ROW trigger.
MODE DB2ROW is valid for both the BEFORE and AFTER activation time.
SET OPTION DBGVIEW = *SOURCEFor more information, see SET OPTION.
The options CLOSQLCSR, CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE TRIGGER statement.
The options DATFMT, DATSEP, TIMFMT, and TIMSEP cannot be used if OLD ROW or NEW ROW is specified.
A call to a procedure that issues a CONNECT, SET CONNECTION, RELEASE, DISCONNECT, COMMIT, ROLLBACK, SET TRANSACTION, and SET RESULT SETS statement is not allowed in the triggered-action of a trigger.
If the trigger is a before trigger, then the SQL-trigger-body must not contain an INSERT, UPDATE, DELETE, ALTER TABLE, COMMENT, any CREATE statement, DECLARE GLOBAL TEMPORARY TABLE, DROP, any GRANT statement, LABEL, REFRESH TABLE, RENAME, or any REVOKE statement. It must not contain a reference to a procedure or function that modifies SQL data.
An UNDO handler is not allowed in a trigger.
All tables, views, aliases, distinct types, user-defined functions, and procedures referenced in the triggered-action must exist at the current server when the trigger is created. The table or view that an alias refers to must also exist when the trigger is created. This includes objects in library QTEMP. While objects in QTEMP can be referenced in the triggered-action, dropping those objects in QTEMP will not cause the trigger to be dropped.
The statements in the triggered-action can invoke a procedure or a user-defined function that can access a server other than the current server if the procedure or user-defined function runs in a different activation group.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.