CREATE TRIGGER

The CREATE TRIGGER statement defines a trigger at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

The privileges held by the authorization ID of the statement must include at least one of the following:

  • Each of the following:
    • The ALTER privilege on the table or view on which the trigger is defined,
    • The SELECT privilege on the table or view on which the trigger is defined,
    • The SELECT privilege on any table or view referenced in the search-condition in the trigger-action,
    • The UPDATE privilege on the table on which the trigger is defined, if the BEFORE UPDATE trigger contains a SET statement that modifies the NEW correlation variable,
    • The privileges required to execute each triggered-SQL-statement, and
    • The system authority *EXECUTE on the library containing the table or view on which the trigger is defined.
  • Database administrator authority

If an INSTEAD OF trigger is added to a view that is not inherently updatable, the *OBJMGT system authority is also required on the view.

In addition, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • *USE on the Add Physical File Trigger (ADDPFTRG) command,
    • *USE on the Create Program (CRTPGM) command
  • Database administrator authority
If the SECURED attribute is specified, or the trigger is secured and OR REPLACE is specified :

If SQL names are specified, and a user profile exists that has the same name as the library into which the trigger is created, and the name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

  • *ALLOBJ and *SECADM special authority
  • Database administrator authority

To replace an existing trigger, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authority of *OBJMGT on the trigger program object
    • All authorities needed to DROP the trigger
  • Database administrator authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACETRIGGERtrigger-nametrigger-definitionWRAPPEDobfuscated-statement-text
trigger-definition
Read syntax diagramSkip visual syntax diagramNO CASCADEBEFOREAFTERINSTEAD OFtrigger-eventON table-nameview-name REFERENCINGOLDROWAScorrelation-nameNEWROWAScorrelation-nameOLD TABLEOLD_TABLEAStable-identifierNEW TABLENEW_TABLEAStable-identifier1FOR EACH STATEMENTFOR EACH ROWMODE DB2SQLMODE DB2ROWtriggered-action
Notes:
  • 1 The same clause must not be specified more than once.
trigger-event
Read syntax diagramSkip visual syntax diagramORINSERTDELETEUPDATEOF,column-name1
Notes:
  • 1 Each trigger-event option can be specified only one time.
triggered-action
Read syntax diagramSkip visual syntax diagramoption-list SET OPTION-statement WHEN(search-condition) SQL-trigger-body
option-list
Read syntax diagramSkip visual syntax diagramCONCURRENT ACCESS RESOLUTIONDEFAULTUSE CURRENTLY COMMITTEDUWAIT FOR OUTCOMEWNOT SECUREDSECUREDENABLEDISABLEPROGRAM NAMEexternal-program-name1
Notes:
  • 1 The options can be specified in any order. The same clause must not be specified more than once.
SQL-trigger-body
Read syntax diagramSkip visual syntax diagramSQL-control-statementfullselectALLOCATE CURSOR-statementALLOCATE DESCRIPTOR-statementALTER FUNCTION-statementALTER MASK-statementALTER PERMISSION-statementALTER PROCEDURE-statementALTER SEQUENCE-statementALTER TABLE-statementASSOCIATE LOCATORS-statementCOMMENT statementCREATE ALIAS-statementCREATE FUNCTION (external scalar)-statementCREATE FUNCTION (external table)-statementCREATE INDEX-statementCREATE MASK-statementCREATE PERMISSION-statementCREATE PROCEDURE (external)-statementCREATE SCHEMA-statementCREATE SEQUENCE-statementCREATE TABLE-statementCREATE TYPE-statementCREATE VIEW-statementDEALLOCATE DESCRIPTOR-statementDECLARE declared temporary table-statementDELETE-statementDESCRIBE-statementDESCRIBE CURSOR-statementDESCRIBE INPUT-statementDESCRIBE PROCEDURE-statementDESCRIBE TABLE-statement
Syntax (continued)
Read syntax diagramSkip visual syntax diagramDROP-statementEXECUTE IMMEDIATE-statementGET DESCRIPTOR-statementGRANT-statementINSERT-statementLABEL-statementLOCK TABLE-statementMERGE-statementREFRESH TABLE-statementRELEASE-statementRELEASE SAVEPOINT-statementRENAME-statementREVOKE-statementSAVEPOINT-statementSELECT INTO-statementSET CURRENT DEBUG MODE-statementSET CURRENT DECFLOAT ROUNDING MODE-statementSET CURRENT DEGREE-statementSET CURRENT IMPLICIT XMLPARSE OPTION-statementSET CURRENT TEMPORAL SYSTEM_TIME-statementSET DESCRIPTOR-statementSET ENCRYPTION PASSWORD-statementSET PATH-statementSET SCHEMA-statementSET TRANSACTION-statementSET transition-variable-statementTRANSFER OWNERSHIP-statementTRUNCATE-statementUPDATE-statementVALUES INTO-statement

Description

OR REPLACE
Specifies to replace the definition for the trigger if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog with the exception that privileges that were granted on the trigger are not affected. This option is ignored if a definition for the trigger does not exist at the current server.
trigger-name

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 about the rules for generating a name, see Rules for Table Name Generation.

NO CASCADE
NO CASCADE is allowed for compatibility with other products and is ignored by Db2® for i.
BEFORE
Specifies that the trigger is a before trigger. The database manager executes the triggered-action before it applies any changes caused by an insert, delete, or update operation on the subject table. It also specifies that the triggered-action does not activate other triggers because the triggered-action of a before trigger cannot contain any updates.

BEFORE must not be specified when a view-name is specified. FOR EACH STATEMENT must not be specified for a BEFORE trigger.

AFTER
Specifies that the trigger is an after trigger. The database manager executes the triggered-action after it applies any changes caused by an insert, delete, or update operation on the subject table. AFTER must not be specified when view-name is also specified.
INSTEAD OF
Specifies that the trigger is an instead of trigger. The associated triggered action replaces the action against the subject view. Only one INSTEAD OF trigger is allowed for each kind of operation on a given subject view. The database manager executes the triggered-action instead of the insert, delete, or update operation on the subject view.

INSTEAD OF must not be specified when table-name is specified. The WHEN clause must not be specified for an INSTEAD OF trigger. FOR EACH STATEMENT must not be specified for an INSTEAD OF trigger.

trigger-event
Specifies that the triggered action associated with the trigger is to be executed whenever one of the events is applied to the subject table or view. Any combination of the events can be specified, but each event (INSERT, DELETE, UPDATE) can only be specified once.
INSERT
Specifies that the triggered-action associated with the trigger is to be executed whenever there is an insert operation on the subject table.

Start of changeA BEFORE INSERT trigger cannot be added to a history table.End of change

DELETE
Specifies that the triggered-action associated with the trigger is to be executed whenever there is a delete operation on the subject table.

A DELETE trigger cannot be added to a table with a referential constraint of ON DELETE CASCADE.

UPDATE
Specifies that the triggered-action associated with the trigger is to be executed whenever there is an update operation on the subject table.

An UPDATE trigger event cannot be added to a table with a referential constraint of ON DELETE SET NULL or ON DELETE SET DEFAULT.

Start of changeA BEFORE UPDATE trigger cannot be added to a history table.End of change

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.

OF column-name, ...
Each column-name specified must be a column of the subject table, and must appear in the list only once. An update operation on any of the listed columns activates the triggered-action. This clause cannot be specified for an INSTEAD OF trigger.
ON table-name
Identifies the subject table of a BEFORE or AFTER trigger definition. The name must identify a base table that exists at the current server, but must not identify a catalog table, a table in QTEMP, or a declared temporary table.
ON view-name
Identifies the subject view of an INSTEAD OF trigger definition. The name must identify a view that exists at the current server, but must not identify a catalog view, or a view in QTEMP. The name must not specify a view that is defined using WITH CHECK OPTION, or a view on which a WITH CHECK OPTION view has been defined, directly or indirectly
REFERENCING
Specifies the correlation names for the transition tables and the table names for the transition tables. Correlation-names identify a specific row in the set of rows affected by the triggering SQL operation. Table-identifiers identify the complete set of affected rows.
Each row affected by the triggering SQL operation is available to the triggered-action by qualifying columns with correlation-names specified as follows:
OLD ROW AS correlation-name
Specifies a correlation name that identifies the values in the row prior to the triggering SQL operation. If the trigger event is insert, the value for every column in OLD ROW is the NULL value.
NEW ROW AS correlation-name
Specifies a correlation name which identifies the values in the row as modified by the triggering SQL operation and any SET statement in a before trigger that has already executed. If the trigger event is delete, the value for every column in NEW ROW is the NULL value.
The complete set of rows affected by the triggering SQL operation is available to the triggered-action by using a temporary table name specified as follows:
OLD TABLE AS table-identifier
Specifies the name of a temporary table that identifies the values in the complete set of affected rows prior to the triggering SQL operation. The OLD TABLE includes the rows that were affected by the trigger if the current activation of the trigger was caused by statements in the SQL-trigger-body of a trigger. If the trigger event is insert, the temporary table is empty.
NEW TABLE AS table-identifier
Specifies the name of a temporary table that identifies the state of the complete set of affected rows as modified by the triggering SQL operation and by any SET statement in a before trigger that has already been executed. If the trigger event is delete, the temporary table is empty.

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 populated 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 populated 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 or INSTEAD OF 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 ROW
Specifies that the database manager executes the triggered-action for each row of the subject table that the triggering operation modifies. If the triggering operation does not modify any rows, the triggered-action is not executed.
FOR EACH STATEMENT
Specifies that the database manager executes the triggered-action only once for the triggering operation. Even if the triggering operation does not modify or delete any rows, the triggered action is still executed once.

FOR EACH STATEMENT cannot be specified for a BEFORE trigger.

FOR EACH STATEMENT cannot be specified for a MODE DB2ROW trigger.

MODE DB2SQL
MODE DB2SQL is valid for AFTER triggers. MODE DB2SQL AFTER triggers are activated after all of the row operations have occurred.

MODE DB2SQL is only valid for BEFORE triggers if a REFERENCING clause is not specified and the trigger table is not referenced in the SQL-trigger-body. MODE DB2SQL BEFORE triggers are activated on each row operation.

MODE DB2ROW
MODE DB2ROW triggers are activated on each row operation.

MODE DB2ROW is valid for both the BEFORE and AFTER activation time.

CONCURRENT ACCESS RESOLUTION
Specifies whether the database manager should wait for data that is in the process of being updated. DEFAULT is the default.
DEFAULT
Specifies that the concurrent access resolution is not explicitly set for this trigger. The value that is in effect when the trigger program is invoked will be used.
WAIT FOR OUTCOME
Specifies that the database manager is to wait for the commit or rollback of data in the process of being updated.
USE CURRENTLY COMMITTED
Specifies that the database manager is to use the currently committed version of the data when encountering data that is in the process of being updated.
When the lock contention is between a read transaction and a delete or update transaction, the clause is applicable to scans with isolation level CS (but not for CS KEEP LOCKS).
SECURED or NOT SECURED
Specifies whether the trigger is considered secure for row access control and column access control. NOT SECURED is the default.
SECURED
Specifies that the trigger is considered secure for row access control and column access control.
SECURED must be specified for a trigger whose subject table is using row access control or column access control. SECURED must also be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.
NOT SECURED
Specifies that the trigger is considered not secure for row access control and column access control.
NOT SECURED must not be specified explicitly or implicitly for a trigger whose subject table is using row access control or column access control. NOT SECURED must also not be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.
ENABLE or DISABLE
Specifies the state for the trigger. ENABLE is the default.
ENABLE
The trigger will be called during I/O operations.
DISABLE
The trigger will not be called during I/O operations.
WRAPPED obfuscated-statement-text
Specifies the encoded definition of the trigger. A CREATE TRIGGER statement can be encoded using the WRAP scalar function.
PROGRAM NAME external-program-name
Specifies the unqualified name of the program to be created for the trigger. external-program-name must be in the form of a program name. It must not be a service program name.
triggered-action
Specifies the action to be performed when a trigger is activated. The triggered-action is composed of one or more SQL statements and by an optional condition that controls whether the statements are executed.
SET OPTION-statement
Specifies the options that will be used to create the trigger. For example, to create a debuggable trigger, the following statement could be included:
SET OPTION DBGVIEW = *SOURCE 
The default values for the options depend on the options in effect at create time. For more information, see SET OPTION.

The options CNULIGN, CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE TRIGGER statement. The COMMIT option is allowed, but ignored.

The options DATFMT, DATSEP, TIMFMT, and TIMSEP cannot be used if OLD ROW or NEW ROW is specified.

WHEN (search-condition)
Specifies a condition that evaluates to true, false, or unknown. The triggered SQL statements are executed only if the search-condition evaluates to true. If the WHEN clause is omitted, the associated SQL statements are always executed.

A WHEN clause must not be specified with an INSTEAD OF trigger.

SQL-trigger-body
Specifies a single SQL-procedure-statement, including a compound statement. See SQL control statements for more information about defining SQL triggers using the SQL Procedural Language (SQL PL).

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.

An UNDO handler is not allowed in a trigger.

All tables, views, aliases, distinct types, global variables, sequences, 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.

All transition variable names are column names of the subject table. System column names of the subject table cannot be used as transition variable names.

The triggered action of a BEFORE trigger on a column of type XML can invoke the XMLVALIDATE function through a SET statement, leave values of type XML unchanged, or assign them to NULL using a SET statement.

Start of changeA dynamic statement in the triggered-action can use 3-part names to access a remote server. A library with the same name as the trigger program's library must exist on the remote server. Static statements in the triggered-action cannot use 3-part names. End of change

Static and dynamic 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.

Notes

Trigger ownership: If SQL names were specified:

  • If a user profile with the same name as the schema into which the trigger is created exists, the owner of the trigger is that user profile.
  • Otherwise, the owner of the trigger is the user profile or group user profile of the thread executing the statement.

If system names were specified, the owner of the trigger is the user profile or group user profile of the thread executing the statement.

Trigger authority: The trigger program object authorities are:

  • When SQL naming is in effect, the trigger program will be created with the public authority of *EXCLUDE, and adopt authority from the schema qualifier of the trigger-name if a user profile with that name exists. If a user profile for the schema qualifier does exist, then the owner of the trigger program will be the user profile for the schema qualifier. Note that the special authorities *ALLOBJ and *SECADM are required to create the trigger program object in the schema qualifier library if a user profile exists that has the same name as the schema qualifier, and the name is different from the authorization ID of the statement. If a user profile for the schema qualifier does not exist, then the owner of the trigger program will be the user profile or group user profile of the thread executing the SQL CREATE TRIGGER statement. The group user profile will be the owner of the trigger program object, only if OWNER(*GRPPRF) was specified on the user's profile who is executing the statement. If the owner of the trigger program is a member of a group profile, and if OWNER(*GRPPRF) was specified on the user's profile, the program will run with the adopted authority of the group profile.
    • For an INSTEAD OF TRIGGER, the privilege associated with the trigger event will be granted to the owner of the view and to the user profile or group user profile of the thread executing the CREATE TRIGGER statement.
  • When System naming is in effect, the trigger program will be created with public authority of *EXCLUDE, and adopt authority from the user or group user profile of the thread executing the SQL CREATE TRIGGER statement.

Execution authorization: The user executing the triggering SQL operation does not need authority to execute a static triggered-SQL-statement. A static triggered-SQL-statement will execute using the authority of the owner of the trigger.

REPLACE rules: When a trigger is recreated by REPLACE:
  • Any existing comment or label is discarded.
  • Authorized users are maintained. The object owner could change.
  • Current journal auditing is preserved.
  • The firing order of the trigger is not maintained.

Activating a trigger: Only insert, delete, or update operations can activate a trigger. A delete operation that occurs as a result of a referential constraint will not activate a trigger. Hence,

  • A trigger with a DELETE trigger event cannot be added to a table with a referential constraint of ON DELETE CASCADE.
  • A trigger with an UPDATE trigger event cannot be added to a table with a referential constraint of ON DELETE SET NULL or ON DELETE SET DEFAULT.

The activation of a trigger may cause trigger cascading. This is the result of the activation of one trigger that executes SQL statements that cause the activation of other triggers or even the same trigger again. The triggered actions may also cause updates as a result of the original modification, which may result in the activation of additional triggers. With trigger cascading, a significant chain of triggers may be activated causing significant change to the database as a result of a single delete, insert or update statement. The number of levels of cascading is limited to 200 or the maximum amount of storage allowed in the job or process, whichever comes first.

Adding triggers to enforce constraints: Adding a trigger to a table that already has rows in it will not cause the triggered actions to be executed. Thus, if the trigger is designed to enforce constraints on the data in the table, the data in the existing rows might not satisfy those constraints.

Considerations for implicitly hidden columns: In the body of a trigger, a trigger transition variable that corresponds to an implicitly hidden column can be referenced. A trigger transition table, that corresponds to a table with an implicitly hidden column, includes that column as part of the transition table.

Likewise, a trigger transition variable will exist for the column that is defined as implicitly hidden. A trigger transition variable that corresponds to an implicitly hidden column can be referenced in the body of a trigger.

Read-only views: The addition of an INSTEAD OF trigger for a view affects the read-only characteristic of the view. If a read-only view has a dependency relationship with an INSTEAD OF trigger, the type of operation that is defined for the INSTEAD OF trigger defines whether the view is deletable, insertable, or updatable.

Transition variable values and INSTEAD OF triggers: All trigger transition variables in an INSTEAD OF trigger are nullable.

The initial values for new transition variables or new transition table columns visible in an INSTEAD OF INSERT trigger are set as follows:

  • If a value is explicitly specified for a column in the INSERT statement, the corresponding new transition variable or new transition table column is that explicitly specified value.
  • If a value is not explicitly specified for a column in the INSERT statement or the DEFAULT keyword is specified, the corresponding new transition variable or new transition table column is:
    • the default value of the underlying table column if the view column is updatable (without the INSTEAD OF trigger) and not based on a generated column or ROWID,
    • otherwise, the null value.

The initial values for new transition variables or new transition table columns visible in an INSTEAD OF UPDATE trigger are set as follows:

  • If a value is explicitly specified for a column in the UPDATE statement, the corresponding new transition variable or new transition table column is that explicitly specified value.
  • If the DEFAULT keyword is explicitly specified for a column in the UPDATE statement, the corresponding new transition variable or new transition table column is:
    • the default value of the underlying table column if the view column is updatable (without the INSTEAD OF trigger) and not based on a generated column or ROWID,
    • otherwise, the null value.
  • Otherwise, the corresponding new transition variable or new transition table column is the existing value of the column in the row.

Obfuscated statements: A CREATE TRIGGER statement can be executed in obfuscated form. In an obfuscated statement, only the trigger name is readable followed by the WRAPPED keyword. The rest of the statement is encoded in such a way that it is not readable but can be decoded by a database server that supports obfuscated statements. Obfuscated statements can be produced by invoking the WRAP scalar function. Any debug options that are specified when the trigger is created from an obfuscated statement are ignored.

A trigger that is created from an obfuscated statement cannot be restored to a release where obfuscation is not supported. Consequently, a table with a trigger created from an obfuscated statement cannot be restored to a release where obfuscation is not supported.

Creating a trigger with the SECURED option: The trigger is considered secure after the CREATE TRIGGER statement is executed. DB2® treats the SECURED attribute as an assertion that declares that the user has established an audit procedure for all activities in the trigger body. If a secure trigger references user-defined functions, Db2 assumes those functions are secure without validation. If those functions can access sensitive data, a user that has security administrator authority needs to ensure that those functions are allowed to access that data and that an audit procedure is in place for those functions, and that all subsequent ALTER FUNCTION statements are being reviewed through this audit process.

A trigger must be secure if its subject table is using row access control or column access control. SECURED must also be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control activated.

Creating a trigger with the NOT SECURED option: The CREATE TRIGGER statement returns an error if the subject table of the trigger is using row access control or column access control, or if its subject table is a view and one or more of the underlying tables in the view is using row access control or column access control.

Transition variable values and row and column access control: Row and column access control is not enforced for transition variables and transition tables. If row or column access control is enforced for the triggering table, row permissions and column masks are not applied to the initial values of transition variables and transition tables. Row and column access control enforced for the triggering table is also ignored for transition variables and transition tables that are referenced in the trigger body or are passed as arguments to user-defined functions invoked within the trigger body. To ensure there are no security concerns for SQL statements accessing sensitive data in transition variables and transition tables, the trigger must be created with the SECURED option. If the trigger is not secure, row access control and column access control cannot be enforced for the triggering table and the CREATE TRIGGER statement returns an error.

Multiple triggers: Multiple triggers that have the same triggering SQL operation and activation time can be defined on a table. The triggers are activated based on the mode and the order in which they were created:

  • MODE DB2ROW triggers (and native triggers created via the ADDPFTRG CL command) are fired first in the order in which they were created
  • MODE DB2SQL triggers are fired next in the order in which they were created

For example, a MODE DB2ROW trigger that was created first is executed first, the MODE DB2ROW trigger that was created second is executed second.

A maximum of 300 triggers can be added to any given source table.

When a trigger is recreated using REPLACE, its position in the activation order is not maintained. It behaves as if the trigger were dropped and created again.

Adding columns to a subject table or a table referenced in the triggered action: If a column is added to the subject table after triggers have been defined, the following rules apply:

  • If the trigger is an UPDATE trigger that was defined without an explicit column list, then an update to the new column will cause the activation of the trigger.
  • If the SQL statements in the triggered-action refer to the triggering table, the new column is not accessible to the SQL statements until the trigger is recreated.
  • The OLD_TABLE and NEW_TABLE transition tables will contain the new column, but the column cannot be referenced unless the trigger is recreated.

If a column is added to any table referenced by the SQL statements in the triggered-action, the new column is not accessible to the SQL statements until the trigger is recreated.

Dropping or revoking privileges on a table referenced in the triggered action: If an object such as a table, view or alias, referenced in the triggered-action is dropped, the access plans of the statements that reference the object will be rebuilt when the trigger is fired. If the object does not exist at that time, the corresponding INSERT, UPDATE or DELETE operation on the subject table will fail.

If a privilege that the creator of the trigger is required to have for the trigger to execute is revoked, the access plans of the statements that reference the object will be rebuilt when the trigger is fired. If the appropriate privilege does not exist at that time, the corresponding INSERT, UPDATE or DELETE operation on the subject table will fail.

Errors executing triggers: If a SIGNAL statement is executed in the SQL-trigger-body, an SQLCODE -438 and the SQLSTATE specified in the SIGNAL statement will be returned.

Other errors that occur during the execution of SQL-trigger-body statements are returned using SQLSTATE 09000 and SQLCODE -723.

Special registers in triggers: The values of the special registers are saved before a trigger is activated and are restored on return from the trigger. The values of the special registers are inherited from the triggering SQL operation.

Transaction isolation: All triggers, when they are activated, perform a SET TRANSACTION statement unless the isolation level of the application program invoking the trigger is the same as the default isolation level of the trigger program. This is necessary so that all of the operations by the trigger are performed with the same isolation level as the application program that caused the trigger to be run. The user may put their own SET TRANSACTION statements in an SQL-control-statement in the SQL-trigger-body of the trigger. If the user places a SET TRANSACTION statement within the SQL-trigger-body of the trigger, then the trigger will run with the isolation level specified in the SET TRANSACTION statement, instead of the isolation level of the application program that caused the trigger to be run.

If the application program that caused a trigger to be activated, is running with an isolation level other than No Commit (COMMIT(*NONE) or COMMIT(*NC)), the operations within the trigger will be run under commitment control and will not be committed or rolled back until the application commits its current unit of work. If ATOMIC is specified in the SQL-trigger-body of the trigger, and the application program that caused the ATOMIC trigger to be activated is running with an isolation level of No Commit (COMMIT(*NONE) or COMMIT(*NC)), the operations within the trigger will not be run under commitment control. If the application that caused the trigger to be activated is running with an isolation level of No Commit (COMMIT(*NONE) or COMMIT(*NC)), then the operations of a trigger are written to the database immediately, and cannot be rolled back.

If both system triggers defined by the Add Physical File Trigger (ADDPFTRG) CL command and SQL triggers defined by the CREATE TRIGGER statement are defined for a table, it is recommended that the system triggers perform a SET TRANSACTION statement so that they are run with the same isolation level as the original application that caused the triggers to be activated. It is also recommended that the system triggers run in the Activation Group of the calling application. If system triggers run in a separate Activation Group (ACTGRP(*NEW)), then those system triggers will not participate in the unit of the work for the calling application, nor in the unit of work for any SQL triggers. System triggers that run in a separate Activation Group are responsible for committing or rolling back any database operations they perform under commitment control. Note that SQL triggers defined by the CREATE TRIGGER statement always run in the caller's Activation Group.

If the triggering application is running with commitment control, the operations of an SQL trigger, and any cascaded SQL triggers, will be captured into a sub-unit of work. If the operations of the trigger and any cascaded triggers are successful, the operations captured in the sub-unit of work will be committed or rolled back when the triggering application commits or rolls back its current unit of work. Any system triggers that run in the same Activation Group as the caller, and perform a SET TRANSACTION to the isolation level of the caller, will also participate in the sub-unit of work. If the triggering application is running without commit control, then the operations of the SQL triggers will also be run without commitment control.

If an application that causes a trigger to be activated is running with an isolation level of No Commit (COMMIT(*NONE) or COMMIT(*NC)), and it issues an INSERT, UPDATE, or DELETE statement that encounters an error during the execution of the statement, no other system or SQL triggers will be activated following the error for that operation. However, some number of changes will already have been performed. If the triggering application is running with commitment control, the operations of any triggers that are captured in a sub-unit of work will be rolled back when the first error is encountered, and no additional triggers will be activated for the current INSERT, UPDATE, or DELETE statement.

Performance considerations: Create the trigger under the isolation level that will most often by used by the application programs that cause the trigger to fire. The SET OPTION statement can be used to explicitly choose the isolation level.

ROW triggers (especially MODE DB2ROW triggers) perform much better than TABLE level triggers.

Considerations for implicitly hidden columns: A transition variable will exist for any column defined as implicitly hidden. In the body of a trigger, a transition variable that corresponds to an implicitly hidden column can be referenced.

Triggered actions in the catalog: At the time the trigger is created, the triggered-action is modified as a result of the CREATE TRIGGER statement:

  • Naming mode is switched to SQL naming.
  • All unqualified object references are explicitly qualified
  • All implicit column lists (for example, SELECT *, INSERT with no column list, UPDATE SET ROW) are expanded to be the list of actual column names.

The modified triggered-action is stored in the catalog.

Renaming or moving a table referenced in the triggered action: Any table (including the subject table) referenced in a triggered-action can be moved or renamed. However, the triggered-action will continue to reference the old name or schema. An error will occur if the referenced table is not found when the triggered-action is executed. Hence, you should drop the trigger and then re-create the trigger so that it refers to the renamed or moved table.

Datetime considerations: If OLD ROW or NEW ROW is specified, the date or time constants and the string representation of dates and times in variables that are used in SQL statements in the triggered-action must have a format of ISO, EUR, JIS, USA, or must match the date and time formats specified when the table was created if it was created using DDS and the CRTPF CL command. If the DDS specifications contain multiple different date or time formats, the trigger cannot be created.

Operations that invalidate triggers: An inoperative trigger is a trigger that is no longer available to be activated. If a trigger becomes invalid, no INSERT, UPDATE, or DELETE operations will be allowed on the subject table or view. A trigger becomes invalid if:

  • The SQL statements in the triggered-action reference the subject table or view, the trigger is a self-referencing trigger, and the table or view is duplicated using the system CRTDUPOBJ CL command, or
  • The SQL statements in the triggered-action reference tables or views in the from library and the objects are not found in the new library when the table or view is duplicated using the system CRTDUPOBJ CL command, or
  • The table or view is restored to a new library using the system RSTOBJ or RSTLIB CL commands, and the triggered-action references the subject table or subject view, the trigger is a self-referencing trigger.

An invalid trigger must first be dropped before it can be recreated by issuing a CREATE TRIGGER statement. Note that dropping and recreating a trigger will affect the activation order of a trigger if multiple triggers for the same triggering operation and activation time are defined for the subject table.

Trigger program object: When a trigger is created, SQL creates a temporary source file that will contain C source code with embedded SQL statements. A program object is then created using the CRTPGM command. The SQL options used to create the program are the options that are in effect at the time the CREATE TRIGGER statement is executed. The program is created with ACTGRP(*CALLER).

The program is created with STGMDL(*SNGLVL). If the trigger runs on behalf of an application that uses STGMDL(*TERASPACE) and also uses commitment control, the entire application will need to run under a job scoped commitment definition (STRCMTCTL CMTSCOPE(*JOB)).

The trigger will execute with the adopted authority of the owner of the trigger.

Examples

Example 1: Create two triggers that track the number of employees that a company manages. The triggering table is the EMPLOYEE table, and the triggers increment and decrement a column with the total number of employees in the COMPANY_STATS table. The COMPANY_STATS table has the following properties:

    CREATE TABLE COMPANY_STATS
      (NBEMP INTEGER,
       NBPRODUCT INTEGER,
       REVENUE DECIMAL(15,0))

This example uses row triggers to maintain summary data in another table.

Create the first trigger, NEW_HIRE, so that it increments the number of employees each time a new person is hired; that is, each time a new row is inserted into the EMPLOYEE table, increase the value of column NBEMP in table COMPANY_STATS by 1.

    CREATE TRIGGER NEW_HIRE
      AFTER INSERT ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
        UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1    

Create the second trigger, FORM_EMP, so that it decrements the number of employees each time an employee leaves the company; that is, each time a row is deleted from the table EMPLOYEE, decrease the value of column NBEMP in table COMPANY_STATS by 1.

    CREATE TRIGGER FORM_EMP
      AFTER DELETE ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
        UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
      END 

Example 2: Create a trigger, REORDER, that invokes user-defined function ISSUE_SHIP_REQUEST to issue a shipping request whenever a parts record is updated and the on-hand quantity for the affected part is less than 10% of its maximum stocked quantity. User-defined function ISSUE_SHIP_REQUEST orders a quantity of the part that is equal to the part's maximum stocked quantity minus its on-hand quantity. The function eliminates any duplicate requests to order the same PARTNO and sends the unique order to the appropriate supplier.

This example also shows how to define the trigger as a statement trigger instead of a row trigger. For each row in the transition table that evaluates to true for the WHERE clause, a shipping request is issued for the part.

    CREATE TRIGGER REORDER
      AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
      REFERENCING NEW_TABLE AS NTABLE
      FOR EACH STATEMENT MODE DB2SQL
      BEGIN ATOMIC
        DECLARE REQUEST_VAR INT;
        SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
        INTO REQUEST_VAR
        FROM NTABLE
        WHERE ON_HAND < 0.10 * MAX_STOCKED;
      END 

Example 3: Assume that table EMPLOYEE contains column SALARY. Create a trigger, SAL_ADJ, that prevents an update to an employee's salary that exceeds 20% and signals such an error. Have the error that is returned with an SQLSTATE of 75001 and a description. This example shows that the SIGNAL SQLSTATE statement is useful for restricting changes that violate business rules.

    CREATE TRIGGER SAL_ADJ
      AFTER UPDATE OF SALARY ON EMPLOYEE
      REFERENCING OLD AS OLD_EMP
                  NEW AS NEW_EMP
      FOR EACH ROW MODE DB2SQL
      WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY *1.20))
      BEGIN ATOMIC
        SIGNAL SQLSTATE '75001'('Invalid Salary Increase - Exceeds 20%');
      END