CREATE TRIGGER

The CREATE TRIGGER statement defines a trigger in a schema and builds a trigger package at the current server.

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

The privilege set that is defined below must include all of the following:

Either of these privileges:

  • The CREATEIN privilege for the schema or all schemas
  • SYSADM or SYSCTRL authority

The TRIGGER privilege on the table. The privilege set 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
  • SYSADM or SYSCTRL authority

The SELECT privilege on the table on which the trigger is defined if any transition variables or transition tables are specified

The SELECT privilege on any table or view to which the search condition of triggered action refers

The EXECUTE privilege on any user-defined function or stored procedure that is invoked in the triggered action

The necessary privileges to invoke the triggered SQL statements in the triggered action

Syntax

CREATE TRIGGER trigger-name {NO CASCADE BEFORE | AFTER}
    {INSERT | DELETE | UPDATE [OF column-name[,...]]} ON table-name
    [REFERENCING {OLD [AS] correlation-name | 
                  NEW [AS] correlation-name |
                  OLD TABLE [AS] identifier | 
                  NEW TABLE [AS] identifier}
    {FOR EACH ROW | FOR EACH STATEMENT} MODE DB2SQL triggered-action]

triggered-action: 

[WHEN (search-condition) BEGIN ATOMIC triggered-SQL-statement;... END]

Description

trigger-name

Names the trigger. The name is implicitly or explicitly qualified by a schema. The name, including the implicit or explicit schema name, must not identify a trigger that exists at the current server.

The 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.

  • The unqualified form of trigger-name is a short SQL identifier. The unqualified name is implicitly qualified with a schema name according to the following rules:

    If the statement is embedded in a program, the schema name of the trigger is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not used, the schema name of the trigger is the owner of the package or plan.

    If the statement is dynamically prepared, the schema name of the trigger is the SQL authorization ID of the process.

  • The qualified form of trigger-name is a short SQL identifier (the schema name) followed by a period and a short SQL identifier. The schema name must not begin with 'SYS' unless the name is 'SYSADM'. The schema name that qualifies the trigger name is the trigger's owner.

The owner of the trigger is determined by how the CREATE TRIGGER statement is invoked:

  • If the statement is embedded in a program, the owner is the authorization ID of the owner of the plan or package.
  • If the statement is dynamically prepared, the owner is the SQL authorization ID in the CURRENT SQLID special register.

NO CASCADE BEFORE

Specifies that the trigger is a before trigger. XDB executes the triggered action before it applies any changes caused by an insert, delete, or update operation on the triggering 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.

AFTER

Specifies that the trigger is an after trigger. XDB executes the triggered action after it applies any changes caused by an insert, delete, or update operation on the triggering table.

INSERT

Specifies that the trigger is an insert trigger. XDB executes the triggered action whenever there is an insert operation on the triggering table.

However, if the insert trigger is defined on PLAN_TABLE, DSN_STATEMNT_TABLE, or DSN_FUNCTION_TABLE, and the insert operation was

caused by XDB adding a row to the table, the triggered action is not be executed.

DELETE

Specifies that the trigger is a delete trigger. XDB executes the triggered action whenever there is a delete operation on the triggering table.

UPDATE

Specifies that the trigger is an update trigger. XDB executes the triggered action whenever there is an update operation on the triggering table.

If you do not specify a list of column names, an update operation on any column of the triggering 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.

ON table-name

Identifies the subject table with which the trigger is associated. The name must identify a base table at the current server. It must not identify a temporary table, an auxiliary table, an alias, a synonym, or a catalog table.

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. A table name identifies the complete set of modified rows.

Each row that is modified by the triggering operation is available to the triggered action by using column names that are qualified with correlation names that are specified as follows:

OLD AS correlation-name Specifies the correlation name that identifies the state of the row prior to the triggering SQL operation.
NEW AS correlation-name Specifies the correlation name that identifies the state of the row as modified by the triggering SQL operation and by any SET statement in a before trigger that has already been executed.

The complete set of rows that is modified by the triggering operation is available to the triggered action by using a temporary table name that is specified as follows:

OLD TABLE AS identifier Specifies the name of a temporary table that identifies the state of the complete set of rows that are modified rows by the triggering SQL operation prior to any actual changes. identifier is a long SQL identifier.
NEW TABLE AS identifier Specifies the name of a temporary table that identifies the state of the complete set of rows as modified by the triggering SQL operation and by any SET statement in a before trigger that has already been executed. identifier is a long SQL identifier.

At most, the trigger definition can include two correlation names (OLD and NEW) and two table names (OLD TABLE and NEW TABLE). All the names must be unique from one another.

FOR EACH ROW

Specifies that XDB 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 XDB executes the triggered action only once for the triggering SQL operation. Even if the triggering SQL operation does not modify any rows, the triggered action is executed once. Do not specify FOR EACH STATEMENT for a before trigger.

MODE DB2SQL

Specifies the mode of the trigger. Currently, XDB supports only MODE DB2SQL.

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 by an optional condition that controls whether the statements are executed.

WHEN (search-condition) Specifies a condition that evaluates to true, false, or unknown. The condition for a before trigger must not include a subselect that references the triggering table.

The triggered SQL statements are executed only if the search condition evaluates to true, or if WHEN is omitted.

BEGIN ATOMIC triggered-SQL-statement;... END Specifies the SQL statements that are to be executed for the triggered action. The statements are executed in the order in which you specify them. The keywords BEGIN ATOMIC and END are required only if you specify more than one SQL statement. In which case, you must enclose the SQL statements in these keywords and end each statement with a semicolon (;).

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

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

Transition variables use the names of the columns in the subject table qualified by a specified 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 search-condition or triggered-SQL-statement of the triggered action wherever a host variable is allowed in the statement if it were issued outside the body of a trigger.

Transition tables can be referenced in the triggered action of an after trigger. Transition tables are read-only. Transition tables also use the name of the columns of the subject table but have a name specified that allows the complete set of affected rows to be treated as a table. The name of the transition table can be referenced in triggered-SQL-statement of the triggered action whenever a table name is allowed in the statement if it were issued outside the body of a trigger. The name of the transition table can be specified in search-condition or triggered-SQL-statement of the triggered action whenever a column name is allowed in the statement if it were issued outside the body of a trigger.

In addition, a transition table can be passed as a parameter to a user-defined function or procedure specifying the TABLE keyword before the name of the transition table. When the function or procedure is invoked, a table locator is passed for the transition table.

A 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.

The implicitly created trigger package

When you create a trigger, DB2 automatically creates a trigger package with the same name as the trigger name. The collection name of the trigger package is the schema name of the trigger, and the version identifier is the empty string. Multiple versions of a trigger package are not allowed.

The user executing the triggering SQL operation does not need authority to execute a trigger package. The trigger package does not need to be in the package list for the plan that is associated with the program that contains the SQL statement.

A trigger package becomes invalid if an object or privilege on which it depends is dropped or revoked. The next time 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 triggering table. Dropping the trigger drops the trigger package; dropping the subject table drops the trigger and the trigger package.

DB2 creates the trigger package with the following attributes:

  • ACTION(ADD)
  • CURRENTDATA(YES)
  • DBPROTOCOL(DRDA)
  • DEGREE(1)
  • DYNAMICRULES(BIND)
  • ENABLE(*)
  • EXPLAIN(NO)
  • FLAG(I)
  • ISOLATION(CS)
  • NOREOPT(VARS) and NODEFER(PREPARE)
  • OWNER(authorization ID)
  • QUERYOPT(1)
  • PATH(path)
  • RELEASE(COMMIT)
  • SQLERROR(NOPACKAGE)
  • QUALIFIER(authorization ID)
  • VALIDATE(BIND)

The 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.

This functionality is only implemented syntactically in the XDB Server operating environment.

Activating a trigger

Only the SQL statements INSERT, DELETE, or UPDATE, or an update or delete operation that occurs as the result of a referential constraint with ON DELETE SET NULL or ON DELETE CASCADE can activate a trigger. Loading a table with the LOAD utility does not activate any triggers that are defined for the table.

This functionality is only implemented syntactically in the XDB Server operating environment.

Simultaneously activated triggers

Multiple triggers that have the same triggering SQL operation and activation time (BEFORE or AFTER) 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; and so on.

This functionality is only implemented syntactically in the XDB Server operating environment.

Adding columns to subject tables or tables that the triggered action references

If a column is added to a table for which a trigger is defined (the subject table), 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 SQL statements in the triggered action refer to the subject table, the new column is not accessible to the SQL statements until the trigger package is rebound.
  • The transition tables contain the new column. If the transition tables are passed to a user-defined function or a stored procedure, the user-defined function or stored procedure must be recreated with the new definition of the table (that is, the function or procedure must be dropped and recreated), and the package for the user-defined function or stored procedure must be rebound.

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

This functionality is only implemented syntactically in the XDB Server operating environment.

Adding triggers to enforce constraints

Creating a trigger on a table that already has rows does 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.

Defining triggers on plan, statement, and function tables: 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 XDB adds rows to the tables.

This functionality is only implemented syntactically in the XDB Server operating environment.

Renaming triggering tables or tables that the triggered action references

You cannot rename a table for which a trigger is defined (the triggering table). Except for the triggering 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.

Dependencies when dropping objects and revoking privileges: The following dependencies apply to a trigger:

  • Dropping the subject table (the table on which the trigger is defined) causes the trigger and its package to also be dropped.
  • 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 referenced synonym has no effect.
  • Dropping a user-defined function 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.

This functionality is only implemented syntactically in the XDB Server operating environment.

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.

Values of special registers

The values of the special registers are saved before a trigger is activated and are restored on return from the

trigger.

Errors when binding triggers

When a CREATE TRIGGER statement is bound, the SQL statements within the triggered action may not be fully parsed. Syntax errors in those statements might not be caught until the CREATE TRIGGER statement is executed.

Errors when executing triggers

Severe errors that occur during the execution of triggered SQL statements are returned with SQLCODE -901, -906, -911, and -913 and the corresponding SQLSTATE. Non-severe errors raised by a triggered SQL statement that is a SIGNAL SQLSTATE statement or that contains a RAISE_ERROR function are returned with SQLCODE -438 and the SQLSTATE that is specified in the SIGNAL SQLSTATE statement or the RAISE_ERROR condition. Other non-severe errors are returned with SQLCODE -723 and SQLSTATE 09000.

Warnings are not returned.

Limiting processor time

XDB's resource limit facility allows you to specify the maximum amount of processor time for a dynamic, manipulative SQL statement (SELECT, INSERT, UPDATE, and DELETE). The execution of a trigger is counted as part of the triggering SQL statement.

Example:

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. 

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

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
Parent topic: SQL Statements