CREATE TRIGGER statement (basic trigger)

The CREATE TRIGGER (basic) statement defines a basic trigger in a schema and builds a trigger package at the current server. Each time that the trigger activates, the trigger package executes one or more times.

FL 500For a description of the differences between basic and advanced triggers, see Triggers.

Invocation for CREATE TRIGGER (basic)

Start of changeThe statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.End of change

Authorization for CREATE TRIGGER (basic)

The privilege set that is defined below must include at least one of the following:

  • The CREATEIN privilege on the schema
  • SYSADM or SYSCTRL authority
  • System DBADM
  • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change

In defining a trigger on a table, the privilege set that is defined below must include SYSADM authority or each of the following:

  • The SELECT privilege on the table on which the trigger is defined, if the REFERENCING clause is included in the trigger definition
  • The SELECT privilege on any table or view in the search-condition of the triggered-action
  • The necessary privileges to invoke the triggered SQL statements in the triggered action
  • The authorization to define a trigger on the table, which must include at least one of the following:
    • The TRIGGER privilege on the table on which the trigger is defined
    • The ALTER privilege on the table on which the trigger is defined
    • DBADM authority on the database that contains the table
    • SYSCTRL authority
    • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change
    • System DBADM authority
    If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

In defining a trigger on a view, the privilege set that is defined below must include SYSADM authority or each of the following:

  • Start of changeThe SELECT privilege on the view on which the trigger is defined, if the REFERENCING clause is included in the trigger definitionEnd of change
  • The SELECT privilege on any table or view in the search-condition of the triggered-action
  • The necessary privileges to invoke the triggered SQL statements in the triggered action
  • The authorization to define a trigger on the view, which must include at least one of the following:
    • Ownership of the view on which the trigger is defined
    • SYSCTRL authority
    • System DBADM authority

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the owner is a role, the implicit schema match does not apply and this role needs to include one of the previously listed conditions.

If the statement is dynamically prepared and is not running in a trusted context for which the ROLE AS OBJECT OWNER clause is specified, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. The specified trigger name can include a schema name (a qualifier). If the schema name is not the same as the SQL authorization ID of the process, one of the following conditions must be met:

  • The privilege set includes SYSADM or SYSCTRL authority.
  • The SQL authorization ID of the process has the CREATEIN privilege on the schema.
If the SECURED option is specified, at least one of the following privileges is required:
  • SECADM authority
  • CREATE_SECURE_OBJECT privilege
Note: If the SEPARATE SECURITY subsystem parameter is set to NO, SYSADM authority has implicit SECADM authority.
Start of change

Syntax for CREATE TRIGGER (basic)

Read syntax diagramSkip visual syntax diagramCREATETRIGGERtrigger-name trigger-definitionWRAPPEDobfuscated-statement-text

trigger-definition

Read syntax diagramSkip visual syntax diagram trigger-activation-time trigger-event ON table-nameview-name REFERENCING1OLDAScorrelation-nameNEWAScorrelation-nameOLD_TABLEAStable-identifierNEW_TABLEAStable-identifier trigger-granularity MODE DB2SQL option-list triggered-action
Notes:
  • 1 The same clause must not be specified more than one time.

trigger-activation-time

Read syntax diagramSkip visual syntax diagram NO CASCADEBEFOREAFTERINSTEAD OF

trigger-event

Read syntax diagramSkip visual syntax diagramINSERTDELETEUPDATEOF,column-name

trigger-granularity

Read syntax diagramSkip visual syntax diagram FOR EACH STATEMENTFOR EACH ROW

triggered-action

Read syntax diagramSkip visual syntax diagramWHEN( search-condition)SQL-trigger-body

SQL-trigger-body

Read syntax diagramSkip visual syntax diagramtriggered-SQL-statementBEGIN ATOMICtriggered-SQL-statement;END

triggered-SQL-statement:

Read syntax diagramSkip visual syntax diagramCALL statementDELETE statement (searched)1common-table-expression1fullselectINSERT statementMERGE statement1REFRESH TABLE statement1SET assignment-statement statement2SIGNAL statementTRUNCATE statement1UPDATE statement (searched)1VALUES statement
Notes:
  • 1 The statement is not allowed in an SQL-trigger-body for a BEFORE trigger.
  • 2 The target of a SET-assignment-statement statement must be a transition variable.

option-list:

Read syntax diagramSkip visual syntax diagram NOT SECUREDSECURED
End of change

Description for CREATE TRIGGER (basic)

trigger-name
Start of changeNames the trigger. The name, including the implicit or explicit schema name, must not identify a trigger that exists at the current server. End of change

Start of changeThe name is also used to create the trigger package; therefore, the name must also not identify a package that is already described in the catalog. The schema name becomes the collection-id of the trigger package. Although trigger-name can be specified as an ordinary or delimited identifier, the name should conform to the rules for an ordinary identifier. Refer to The implicitly created trigger package for additional information.End of change

Start of changeThe schema name can be 'SYSTOOLS' if the privilege set includes the SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM', 'SYSIBMADM', or 'SYSPROC'.End of change

Start of changeNO CASCADE BEFOREEnd of change
Start of changeSpecifies that the trigger is a before trigger. Db2 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, REFRESH TABLE, or TRUNCATE SQL statements.

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

End of change
AFTER
Specifies that the trigger is an after trigger. Db2 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 if 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 type of operation on a given subject view. Db2 executes the triggered-action instead of the insert, update, or delete operation on the subject view.

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

Start of changeIn the view definition, the non-numeric columns of the outermost SELECT list of the fullselect must use the same encoding scheme.End of change

ON table-name
Identifies the subject table of the BEFORE or AFTER trigger definition. The name must identify a base table that exists at the current server. Start of changeIt must not identify a materialized query table, a clone table, a temporary table, an auxiliary table, an alias, a synonym, a real-time statistics table, an accelerator-only table, a catalog table, or a directory table.End of change
ON view-name
Identifies the subject view of the INSTEAD OF trigger definition. The name must identify a view that exists at the current server.

view-name must not specify a view where any of the following conditions are true:

  • The view is defined with the WITH CASCADED CHECK option (a symmetric view)
  • The view on which a symmetric view has been defined
  • The view references data that is encoded with different encoding schemes or CCSID values
  • The view has a column that is a ROWID column
  • The view has a column that is based on an underlying column of any of the following types:
    • A LOB, XML, or ROWID column
    • An identity column
    • A security label column
    • A row change timestamp column
    • A row-begin column
    • A row-end column
    • A transaction start ID column
  • The view has columns that have field procedures
  • All of the underlying tables of the view are catalog tables
  • All of the underlying tables of the view are created global temporary tables
  • All of the underlying tables of the view are clone tables
  • The view has other views that are dependent on it
REFERENCING
Specifies the correlation names for the transition variables and the table names for the transition tables. For the rows in the subject table that are modified by the triggering SQL operation (insert, delete, or update), a correlation name identifies the columns of a specific row. table-identifiers identify the complete set of affected rows. Transition variables with XML types cannot be referenced inside of a trigger. If the column of a transition table is referenced, the data type of the column cannot be XML.

Each row that is affected by the triggering SQL operation is available to the triggered action by qualifying column names with correlation-names that are specified as follows:

Start of changeOLD AS correlation-nameEnd of change
Start of changeSpecifies the correlation name that identifies the values in the row prior to the triggering SQL operation.End of change
Start of changeNEW AS correlation-nameEnd of change
Start of changeSpecifies the correlation name that identifies the values in the row as modified by the triggering SQL operation and by any assignment statement in a before trigger that has already been executed.End of change

The complete set of rows that are affected by the triggering operation is available to the triggered action by using table-identifiers that are specified as follows:

OLD_TABLE AS table-identifier
Specifies the name of a temporary table that identifies the values in the complete set of rows that are modified rows by the triggering SQL operation prior to any actual changes.
Start of changeNEW_TABLE AS table-identifierEnd of change
Start of changeSpecifies the name of a temporary table that identifies the values in the complete set of rows as modified by the triggering SQL operation and by any assignment statement in a before trigger that has already been executed. End of change

Only one OLD and one NEW correlation-name can be specified for a trigger. Only one OLD_TABLE and one NEW_TABLE table-identifier can be specified for a trigger. All of the correlation-names and table-identifiers must be unique from one another.

Table 1 summarizes the allowable combinations of transition variables and transition tables that you can specify for the various trigger types. 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, the 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.

Start of changeThe NEW 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 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 assignment statements in the triggered action of BEFORE triggers.End of change

Table 1. Allowable combinations of attributes in a trigger definition
Granularity Activation time Triggering SQL operation Transition variables allowed1 Transition tables allowed1
FOR EACH ROW BEFORE DELETE OLD None
INSERT NEW None
UPDATE OLD, NEW None
AFTER DELETE OLD OLD_TABLE
INSERT NEW NEW_TABLE
UPDATE OLD, NEW OLD_TABLE, NEW_TABLE
INSTEAD OF DELETE OLD OLD_TABLE
INSERT NEW NEW_TABLE
UPDATE OLD, NEW OLD_TABLE, NEW_TABLE
FOR EACH STATEMENT AFTER DELETE None OLD_TABLE
INSERT None NEW_TABLE
UPDATE None OLD_TABLE, NEW_TABLE
Note:
  1. If a transition table or variable is referenced where it is not allowed, an error is returned.

A transition variable that has a character data type inherits the subtype and CCSID of the column of the subject table. During the execution of the triggered action, the transition variables are treated like host variables. Therefore, character conversion might occur. However, unlike a host variable, a transition variable can have the bit data attribute, and character conversion never occurs for bit data. A transition variable is considered to be bit data if the column of the table to which it corresponds is bit data.

You cannot modify a transition table; transition tables are read-only. Although a transition table does not inherit any edit or validation procedures from the subject table, it does inherit the encoding scheme and field procedures of the subject table.

The scope of each correlation-name and each table-identifier is the entire trigger definition.

trigger-granularity
FOR EACH ROW or FOR EACH STATEMENT
Specifies the conditions for which Db2 executes the triggered action.
FOR EACH ROW
Specifies that Db2 executes the triggered action for each row of the subject table that the triggering SQL operation modifies. If the triggering SQL operation does not modify any rows, the triggered action is not executed..
FOR EACH STATEMENT
Specifies that Db2 executes the triggered action only one time for the triggering operation. Even if the triggering operation does not modify or delete any rows, the triggered action is executed one time.

FOR EACH STATEMENT must not be specified for a BEFORE or INSTEAD OF trigger.

MODE DB2SQL
Start of changeIndicates that a basic trigger is to be created.End of change
NOT SECURED or SECURED
Specifies whether the trigger is considered secure. NOT SECURED is the default.
SECURED
Specifies the trigger is considered secure.

SECURED must be specified for a trigger 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.

NOT SECURED
Specifies the trigger is considered not secure.

NOT SECURED must not be specified 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.

trigger-event
Specifies that the triggered action that is associated with the trigger is to be executed when the trigger event is applied to the subject table or view.
INSERT
Specifies that the trigger is an insert trigger. Db2 executes the triggered action whenever there is an insert operation on the subject table. However, if the insert trigger is defined on any explain table, and the insert operation was caused by Db2 adding a row to the table, the triggered action is not to be executed.
DELETE
Specifies that the trigger is a delete trigger. Db2 executes the triggered action whenever there is a delete operation on the subject table.
UPDATE
Specifies that the trigger is an update trigger. Db2 executes the triggered action whenever there is an update operation on the subject table.

If you do not specify a list of column names, 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 that you specify 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.

UPDATE OF column-name cannot be specified for an INSTEAD OF trigger.

triggered-action
Specifies the action to be performed when the trigger is activated. The triggered-action is composed of one or more SQL statements and an optional condition that controls whether the statements are executed.
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.

The WHEN clause must not be specified for an INSTEAD OF trigger.

SQL-trigger-body
Start of changeSpecifies the SQL statements that are to be executed for the triggered action. Only certain SQL statements can be specified in the SQL-trigger-body.End of change
triggered-SQL-statement
Specifies the SQL statements that can be specified as the only statement in a trigger body.
BEGIN ATOMIC triggered-SQL-statement,... END
Specifies a list of SQL statements that are to be executed for the triggered action. The statements are executed in the order in which they are specified.

Only certain SQL statements can be specified in the SQL-trigger-body.

Start of changeThe trigger body must not contain a statement that is not supported. Table 2 shows the list of allowable SQL statements, which differs depending on whether the trigger is being defined as BEFORE, AFTER, or INSTEAD OF. An 'X' in the table indicates that the statement is valid.End of change
Table 2. Allowable SQL statements
SQL statement Trigger activation time
BEFORE AFTER INSTEAD OF
CALL X X X
DELETE (searched)   X X
fullselect X X X
INSERT   X X
MERGE   X X
REFRESH TABLE   X X
SET transition variable X    
SIGNAL X X X
TRUNCATE   X X
UPDATE (serached)   X X
VALUES X X X
The statements in the triggered action have these restrictions:
  • They must not refer to host variables, parameter markers, undefined transition variables, or declared temporary tables.
  • They must only refer to a table or view that is at the current server.
  • They must only invoke a stored procedure or user-defined function that is at the current server. An invoked routine can, however, access a server other than the current server.
  • They must not contain a fullselect that refers to the subject table if the trigger is defined as BEFORE.
  • They must not modify a column that is part of a BUSINESS_TIME period.
  • If the trigger is a before trigger:
    • The trigger body must not contain a DELETE, INSERT, MERGE, REFRESH TABLE, TRUNCATE, UPDATE statement, or SELECT FROM data-change-statement, and must not reference a procedure or function that is defined as MODIFIES SQL DATA.
    • The trigger body must not, directly or indirectly, issue a CALL statement for a procedure containing one of the following statements:
      • ALTER
      • COMMENT
      • CREATE
      • DELETE
      • DROP
      • EXCHANGE
      • GRANT
      • LABEL
      • LOCK TABLE
      • MERGE
      • REFRESH TABLE
      • RENAME
      • REVOKE
      • TRUNCATE
      • UPDATE

A CREATE TRIGGER statement cannot contain a hexadecimal graphic string (GX) constant.

The triggered action can refer to the values in the set of affected rows. This action is supported through the use of transition variables and transition tables.

All tables, views, aliases, sequences, roles, user-defined data types, user-defined functions, and procedures referenced in the triggered-action must exist at the current server when the version of the trigger is defined. The table or view that an alias refers to must also exist when the version of the trigger is defined.

Start of changeWRAPPED obfuscated-statement-textEnd of change
Start of changeFL 500Specifies the encoded definition of the trigger. A CREATE TRIGGER statement can be encoded using the WRAP scalar function.

WRAPPED must not be specified on a static CREATE statement.

End of change

Notes for CREATE TRIGGER (basic)

Owner privileges:
When an INSTEAD OF trigger is defined, the associated privilege (INSERT, UPDATE, or DELETE on the view) is given to the owner of the view. The owner is granted the privilege with the ability to grant that privilege to others. For more information about ownership of an object, see Authorization, privileges, permissions, masks, and object ownership.
Execution authorization:
The user executing the triggering SQL operation does not need authority to execute a SQL-trigger-body. An SQL-trigger-body executes using the authority of the owner of the trigger.
Activating a trigger:
Only insert, delete, or update operations can activate a trigger. The activation of a trigger might cause trigger cascading.Trigger cascading 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 might also cause updates as a result of the original modification, which can result in the activation of additional triggers. With trigger cascading, a significant chain of triggers might be activated, causing a significant change to the database as a result of a single insert, delete, or update operation.

Loading a table with the LOAD utility does not activate any triggers that are defined for the table if the SHRLEVEL NONE option is specified or accepted as the default. If the LOAD statement includes the SHRLEVEL CHANGE option, triggers are activated when loading a table with the LOAD utility.

Adding triggers to enforce constraints:
Adding a trigger on a table that already has rows in it will not cause the triggered-action 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.
Multiple triggers:
Multiple triggers that have the same triggering SQL operation and activation time can be defined on a table. The triggers are activated in the order in which they were created. For example, the trigger that was created first is executed first; the trigger that was created second is executed second.
Transition variables and transition tables:
The triggered-action can refer to the values in the set of affected rows. This action is supported through the use of transition variables and transition tables.

Start of changeA transition variable has the same name, data type, and nullability attribute as the corresponding column of the table that the trigger is defined on. A transition variable contains the value of a column in an affected row. A transition variable is qualified by a correlation name that identifies whether the reference is to the old value (before the update) or the new value (after the update). A transition variable can be referenced in the search condition, or in an SQL statement in the trigger body, wherever a variable would be allowed if the reference was made outside the body of a trigger. A new transition variable can be assigned a value in a before update or insert trigger. The values of the updated rows include the changes from any assignments to transition variables in the triggered-action of a before trigger.End of change

A transition table contains the complete set of affected rows: either the old values (before the update) or the new values (after the update). A transition table is read-only, and can be referenced in the triggered-action of an after or instead of trigger. A column of a transition table has the same name, data type, and null attribute as the corresponding column of the table that the trigger is defined on. A transition table is referenced using an associated table identifier that allows the complete set of affected rows to be treated as a table. A transition table can be referenced in a search condition, or in an SQL statement in the trigger body, wherever a table would be allowed if the reference was made outside the body of a trigger. In addition, a transition table can be passed as an argument to a user-defined function or procedure, specifying the TABLE keyword before the table identifier for the transition table. When the function or procedure is invoked, a table locator is passed for the transition table.

Start of changeA transition variable or transition table is not affected after being returned from a procedure invoked from within a triggered action regardless of whether the corresponding parameter was defined in the CREATE PROCEDURE statement as IN, INOUT, or OUT.End of change

Start of changeConsiderations for table-locator-reference elements:End of change
Start of changetable-locator-reference must not be specified in the body of a trigger.End of change
Considerations for implicitly hidden columns:
In the body of a trigger, a transition variable that corresponds to an implicitly hidden column can be referenced. A transition table, that corresponds to a table with an implicitly hidden column, includes that column as part of the transition table. Likewise, a transition variable will exist for the column that is defined as implicitly hidden. A transition variable that corresponds to an implicitly hidden column can be referenced in the body of a trigger.
Identifier resolution:
Names that are the same should be explicitly qualified. Qualifying a name clearly indicates whether the name refers to a column, a transition variable, or a global variable. If the name is not qualified or is qualified but is still ambiguous, the following rules describe how the name is resolved. If correlation names are specified for both old and new transition variables, a reference to a transition variable must be qualified with the associated correlation name. The name is resolved by checking for a match in the following order:
  • If the tables and views specified in the trigger body exist at the time the trigger is created, the name is first checked as a column name at the current server. If the name is found as a column name, but the privilege set that is used to issue the CREATE statement does not have the proper authority to access the table or view, an error is returned.
  • If the name is not found as a column, the name is checked as a transition variable name.
  • If the name is not found as a transition variable, the name is checked as a global variable.
  • If the name is still not resolved, an error is returned.
Considerations for transition variables in AFTER and INSTEAD OF triggers:
Transition variables cannot be modified in an AFTER or INSTEAD OF trigger.
Invalidation of packages:
This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors. For more information, see Changes that invalidate packages.
Invalidation of cached dynamic SQL statements:
This statement might invalidate cached dynamic SQL statements that depend on target objects, and sometimes other related objects through cascading effects. For more information, see Invalidation of cached dynamic statements.
Considerations for INSTEAD OF triggers:
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.

The creation of an INSTEAD OF trigger causes dependent packages, plans, and statements in the dynamic statement cache to be marked invalid if the view definition is not read-only.

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

  • If a value is explicitly specified for a column in the insert operation, the corresponding new transition variable is that explicitly specified value.
  • If a value is not explicitly specified for a column in the insert operation or the DEFAULT clause is specified, the corresponding new transition variable is:
    • the default value of the underlying table column if the view column is updatable (without the INSTEAD OF trigger)
    • otherwise, the null value

    If a view column is not nullable and does not have a default, the value must be explicitly specified in the insert operation.

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

  • If a value is explicitly specified for a column in the update operation, the corresponding new transition variable is that explicitly specified value
  • If the DEFAULT clause is explicitly specified for a column in the update operation, the corresponding new transition variable is:
    • the default value of the underlying table column if the view column is updatable (without the INSTEAD OF trigger)
    • otherwise, the null value

    If a view column is not nullable and does not have a default, the value must be explicitly specified in the update operation.

  • Otherwise, the corresponding new transition variable is the existing value of the column in the row.
Considerations for a MERGE statement:
Start of changeThe MERGE statement can execute insert, delete, and update operations.End of change The applicable triggers are activated for the MERGE statement for the data change operations that are executed.
Considerations for triggers that are defined on tables that contain XML columns:
Although a trigger can be defined on a table that contains an XML column:
  • Start of changeAn XML column cannot be referenced with a trigger transition variable in the trigger body. End of change
  • Start of changeAn XML column of a transition table cannot be referenced in the trigger body. End of change
Creating a trigger with the SECURED option:
Typically, the security administrator will examine the data that is accessed by a trigger, ensure that it is secure, and grant the CREATE_SECURE_OBJECT privilege to someone who requires the privileges to create a secured trigger. After the trigger is created, the security administrator will revoke the CREATE_SECURE_OBJECT privilege from the owner of the trigger.

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, the user with SECADM authority needs to ensure that those functions are allowed to access that data and that an audit procedure is in place for all versions of those functions, and that all subsequent ALTER FUNCTION statements or changes to external packages are being reviewed by 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.

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 the trigger is for a view and one or more of the underlying tables in the view definition is using row access control or column access control.
Row and column access control that is not enforced for transition variables and transition tables:
If row access control or column access control is enforced for the subject table of the trigger, row permissions and column masks are not applied to the initial values of transition variables and transition tables. Row access control and column access control is enforced for the triggering table, but is ignored for transition variables and transition tables that are referenced in the body of the trigger body or are passed as arguments to user-defined functions that are invoked in the body of the trigger. To ensure that there are no security concerns for SQL statements accessing sensitive data in transition variables and transition tables in the trigger action, the trigger must be created with the SECURED option. If a trigger is not secure, the CREATE TRIGGER statement returns an error.
Restrictions involving pending definition changes:
CREATE TRIGGER is not allowed if the trigger is defined on a table for which there are pending definition changes.
Considerations for the special plan, statement, and function tables for EXPLAIN:
You can create a trigger on PLAN_TABLE, DSN_STATEMNT_TABLE, or DSN_FUNCTION_TABLE. However, insert triggers that are defined on these tables are not activated when Db2 adds rows to the tables.
Dependent objects:
A trigger is dependent on the triggering table and objects that are referenced in the trigger body.
Adding a column 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 list of column names, an update to the new column activates the trigger.
  • If the subject table is referenced in the triggered-action, the new column is not accessible to the SQL statements until the trigger package is rebound.
  • The OLD_TABLE and the NEW_TABLE transition tables contain the new column, but the column cannot be referenced unless the trigger is re-created. If the transition tables are passed to a user-defined function or a stored procedure, the user-defined function or stored procedure must be re-created with the new definition of the table (that is, the function or procedure must be dropped and re-created), and the package for the user-defined function or stored procedure must be rebound.

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

Dropping a column from a subject table or a table referenced in the triggered-action:
A column cannot be dropped from a table for which a trigger is defined.
Altering the attributes of a column that the triggered action references:
If a column is altered in the table on which the trigger is defined (the subject table), the alter is processed, and the dependent trigger packages are invalidated.
Renaming the table for which the trigger is defined, or tables referenced in the triggered-action:
You cannot rename a table for which a trigger is defined (the subject table). Except for the subject table, you can rename any table to which the SQL statements in the triggered action refer. After renaming such a table, drop the trigger and then re-create the trigger so that it refers to the renamed table.
Dropping the table or view for which a trigger is defined:
If the table or view that a trigger is defined on is dropped, the trigger is also dropped.
Effects of dropping or revoking privileges on an object referenced in the triggered-action:
Dropping an object that is referenced in the body of a trigger, or revoking a privilege to an object that is referenced in the body of a trigger, might have the following results:
  • Dropping any table, view, alias, or index that is referenced or used within the SQL statements in the triggered action causes the trigger and its package to be invalidated.
  • Dropping a synonym that is referenced does not have any effect on the trigger or its package.
  • Dropping a user-defined function that is referenced by the SQL statements in the triggered action is not allowed. An error occurs.
  • Dropping a sequence that is referenced by the SQL statements in the triggered action is not allowed. An error occurs.
  • Revoking a privilege on which the trigger depends causes the trigger and its package to be invalidated. If the appropriate privilege does not exist when the trigger package is rebound, the SQL statement that references the object fails.
Error handling in triggers:
A trigger can return errors, like other SQL statements. Applications need to account for the possible errors that can be expected when a trigger is invoked.
Triggers and global variables:
The content of a global variable that is referenced by a trigger is inherited from the triggering SQL operation (delete, insert, or update).
Special registers:
The values of the special registers that are used in the trigger body are determined when the trigger is activated. The value of the CURRENT PACKAGESET special register is set to the schema name of the trigger, and the values of the other special registers are inherited from the triggering SQL operation (insert, update, or delete). Special register values are saved when a trigger is activated and are restored on return from the trigger. The CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP special registers are not restored on return from the trigger.
Result sets for stored procedures:
If a trigger invokes a stored procedure that returns result sets, the application that activated the trigger cannot access those result sets.
Transaction isolation:
All of the statements in the SQL-trigger-body run under the isolation level that is in effect for the trigger.
Limiting processor time:
Start of changeThe Db2 resource limit facility allows you to specify the maximum amount of processor time for a dynamic, manipulative SQL statement such as SELECT or SQL data change statements. The execution of a trigger is counted as part of the triggering SQL statement. End of change
Errors binding triggers:
When a CREATE TRIGGER statement is bound, the SQL statements within the triggered action might not be fully parsed. Syntax errors in those statements might not be caught until the CREATE TRIGGER statement is executed.
Characteristics of the package that is generated for a trigger:
The package that is associated with the trigger is named as follows:
  • location is set to the value of the CURRENT SERVER special register.
  • collection-id (schema) for the package is the same as the schema qualifier of the trigger.
  • package-id is the same as the name of the trigger.
  • version-id is an empty string.

Start of changeDb2 creates the trigger package with the following initial attributes (some of these attributes can be modified using the REBIND TRIGGER PACKAGE command):End of change

Start of change
  • ACTION(ADD)
  • ARCHIVESENSITIVE(YES)
  • BUSTIMESENSITIVE(YES)
  • CURRENTDATA(NO)
  • DBPROTOCOL(DRDA)
  • DEGREE(1)
  • DESCSTAT(value from the DESCSTAT subsystem parameter)
  • DYNAMICRULES(BIND)
  • ENABLE(*)
  • ENCODING(0)
  • EXPLAIN(NO)
  • FLAG(I)
  • ISOLATION(CS)
  • REOPT(NONE)
  • NODEFER(PREPARE)
  • OPTHINT
  • OWNER(authorization ID) or ROLE
  • PATH(path)
  • RELEASE(COMMIT)
  • ROUNDING(value from the CURRENT DECFLOAT ROUNDING MODE special register)
  • SQLERROR(NOPACKAGE)
  • SYSTIMESENSITIVE(YES)
  • QUALIFIER(authorization ID)
  • VALIDATE(BIND)
End of change

Start of changeThe values of OWNER, QUALIFIER, and PATH are set depending on whether the CREATE TRIGGER statement is embedded in a program or issued interactively. If the statement is embedded in a program, OWNER and QUALIFIER are the owner and qualifier of the package or plan. PATH is the value from the PATH bind option. If the statement is issued interactively, both OWNER and QUALIFIER are the SQL authorization ID. PATH is the value in the CURRENT PATH special register.End of change

Start of changeMultiple versions of a trigger package are not allowed. Use the REBIND TRIGGER PACKAGE command to explicitly rebind the basic trigger package. To specify the name of a trigger package for the bind commands, the trigger name must conform to the rules for an ordinary identifier.End of change

A trigger package becomes invalid if an object or privilege on which it depends is dropped or revoked. The next time that the trigger is activated, Db2 attempts to rebind the invalid trigger package. If the automatic rebind is unsuccessful, the trigger package remains invalid.

You cannot create another package from the trigger package, such as with the BIND COPY command. The only way to drop a trigger package is to drop the trigger or the subject table. Dropping the trigger drops the trigger package; dropping the subject table drops the trigger and the trigger package.

Each time that the trigger activates, the trigger package executes one or more times.

Considerations for SQL processor programs:
SQL processor programs, such as SPUFI, the Db2 command line processor, and DSNTEP2, might not correctly parse SQL statements in the triggered action that are ended with semicolons. These processor programs accept multiple SQL statements, each separated with a terminator character, as input. Processor programs that use a semicolon as the SQL statement terminator can truncate a CREATE TRIGGER statement with embedded semicolons and pass only a portion of it to Db2. Therefore, you might need to change the SQL terminator character for these processor programs. For information on changing the terminator character for SPUFI and DSNTEP2, see Setting the SQL terminator character in a SPUFI input data set and DSNTEP2 and DSNTEP4 sample programs.
Start of changeApplication compatibility level:End of change
Start of changeThe SQL statements in the trigger body must conform to the behavior specified by the application compatibility option in effect. The application compatibility value used to process the trigger is recorded in the APPLCOMPAT column of the SYSIBM.SYSPACKAGE catalog table.End of change
Alternative syntax and synonyms:
To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following keywords:
  • OLD TABLE as a synonym for OLD_TABLE
  • NEW TABLE as a synonym for NEW_TABLE
Start of changeObfuscated statements:End of change
Start of changeFL 500A CREATE TRIGGER statement can be executed in obfuscated form. In an obfuscated statement, only the trigger name and the WRAPPED keyword are readable. 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. The WRAP scalar function produces obfuscated statements. Any debug options that are specified when the trigger is created from an obfuscated statement are ignored.End of change

Examples for CREATE TRIGGER (basic)

Example 1

Create two triggers that track the number of employees that a company manages. The subject 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 tables have these columns:

  • EMPLOYEE table: ID, NAME, ADDRESS, and POSITION
  • COMPANY_STATS table: NBEMP, NBPRODUCT, and REVENUE

This example shows the use of transition variables in a row trigger 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. Start of change
   CREATE TRIGGER NEW_HIRE
      AFTER INSERT ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
        UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
      END
End of change
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. Start of change
   CREATE TRIGGER FORM_EMP
      AFTER DELETE ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
        UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
      END
End of change
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 also ensures that the request is sent to the appropriate supplier.

The parts records are in the PARTS table. Although the table has more columns, the trigger is activated only when columns ON_HAND and MAX_STOCKED are updated.

Start of change
 CREATE TRIGGER REORDER
     AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
     REFERENCING NEW AS NROW
     FOR EACH ROW
     MODE DB2SQL
     WHEN (NROW.ON_HAND < 0.10 * NROW.MAX_STOCKED)
     BEGIN ATOMIC
       VALUES(ISSUE_SHIP_REQUEST(NROW.MAX_STOCKED - NROW.ON_HAND, NROW.PARTNO));
     END
End of change
Example 3

Repeat the scenario in Example 2 except use a fullselect instead of a VALUES statement to invoke the user-defined function. 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.

Start of change
 CREATE TRIGGER REORDER
     AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
     REFERENCING NEW_TABLE AS NTABLE
     FOR EACH STATEMENT MODE DB2SQL
       BEGIN ATOMIC
         SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
           FROM NTABLE
         WHERE (ON_HAND < 0.10 * MAX_STOCKED);
     END
End of change
Example 4
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 statement is useful for restricting changes that violate business rules. Start of change
   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
End of change
Example 5
Assume that the following statements create a table, WEATHER (which stores temperature values in Fahrenheit), and a view, CELSIUS_WEATHER for users who prefer to work in Celsius instead of Fahrenheit:
   CREATE TABLE WEATHER
      (CITY VARCHAR(25),
       TEMPF DECIMAL(5,2));
   CREATE VIEW CELSIUS_WEATHER (CITY, TEMPC) AS
      SELECT CITY, (TEMPF-32)/1.8 
      FROM WEATHER;
The following INSTEAD OF trigger is used on the CELSIUS_WEATHER view to convert Celsius values to Fahrenheit values and then insert the Fahrenheit value into the WEATHER table: Start of change
   CREATE TRIGGER CW_INSERT INSTEAD OF INSERT
      ON CELSIUS_WEATHER
      REFERENCING NEW AS NEWCW
      FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
           INSERT INTO WEATHER VALUES
             (NEWCW.CITY, 
              1.8*NEWCW.TEMPC+32);
        END
End of change
Start of changeStart of changeExample 6End of changeEnd of change
Start of changeConsider an application that records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY. CURRENTQUOTE has columns SYMBOL, QUOTE, and STATUS. QUOTEHISTORY has columns SYMBOL, QUOTE, and QUOTE_TIMESTAMP.

When the QUOTE column of CURRENTQUOTE is updated, the new quote must be copied, with a timestamp, to the QUOTEHISTORY table. Also, the STATUS column of CURRENTQUOTE must be updated to reflect whether the stock is:

Start of change
1 Rising in value
2 At a new high for the year
3 Dropping in value
4 At a new low for the year
5 Steady in value
End of change

CREATE TRIGGER statements that accomplish these tasks are as follows:

Trigger STOCK_STATUS sets the status:

CREATE TRIGGER STOCK_STATUS
 NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
 REFERENCING NEW AS NEWQUOTE
 OLD AS OLDQUOTE
 FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
   SET NEWQUOTE.STATUS = CASE
   WHEN NEWQUOTE.QUOTE >
    (SELECT MAX(QUOTE)
    FROM QUOTEHISTORY
    WHERE SYMBOL = NEWQUOTE.SYMBOL
    AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
   THEN 'High'
   WHEN NEWQUOTE.QUOTE <
    (SELECT MIN(QUOTE)
    FROM QUOTEHISTORY
    WHERE SYMBOL = NEWQUOTE.SYMBOL
    AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
   THEN 'Low'
   WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE THEN 'Rising'
   WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE THEN 'Dropping'
   WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE THEN 'Steady'
  END;
END

Trigger RECORD_HISTORY records changes in the QUOTEHISTORY table:

CREATE TRIGGER RECORD_HISTORY
 AFTER UPDATE OF QUOTE ON CURRENTQUOTE
 REFERENCING NEW AS NEWQUOTE FOR EACH ROW MODE DB2SQL
 BEGIN ATOMIC
  INSERT INTO QUOTEHISTORY VALUES (NEWQUOTE.SYMBOL,
  NEWQUOTE.QUOTE,
  CURRENT TIMESTAMP);
 END
End of change