SQL Reference

CREATE TRIGGER

The CREATE TRIGGER statement defines a trigger in the database.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

If the authorization ID of the statement does not have SYSADM or DBADM authority, the privileges that the authorization ID of the statement holds (without considering PUBLIC or group privileges) must include all of the following as long as the trigger exists:

If a trigger definer can only create the trigger because the definer has SYSADM authority, then the definer is granted explicit DBADM authority for the purpose of creating the trigger.

Syntax 

>>-CREATE TRIGGER--trigger-name----+-NO CASCADE BEFORE-+-------->
                                   '-AFTER-------------'
 
>-----+-INSERT-----------------------------+--ON--table-name---->
      +-DELETE-----------------------------+
      '-UPDATE--+------------------------+-'
                |    .-,--------------.  |
                |    V                |  |
                '-OF----column-name---+--'
 
>-----+----------------------------------------------------------------------+>
      |              .----------------------------------------------------.  |
      |              V   (1)    (2)          .-AS-.                       |  |
      '-REFERENCING-------------------+-OLD--+----+--correlation-name--+--+--'
                                      |     .-AS-.                     |
                                      +-NEW-+----+--correlation-name---+
                                      |           .-AS-.               |
                                      +-OLD_TABLE-+----+--identifier---+
                                      |           .-AS-.               |
                                      '-NEW_TABLE-+----+--identifier---'
 
>-----+-FOR EACH ROW---------------+--MODE DB2SQL--------------->
      |  (3)                       |
      '--------FOR EACH STATEMENT--'
 
>-----| triggered-action |-------------------------------------><
 
triggered-action
 
|--+-------------------------------+---------------------------->
   '-WHEN--(--search-condition--)--'
 
>-----+-triggered-SQL-statement--------------------------------+-|
      |               .-------------------------------.        |
      |               V                               |        |
      '-BEGIN ATOMIC-----triggered-SQL-statement--;---+---END--'

Notes:

  1. OLD and NEW may only be specified once each.

  2. OLD_TABLE and NEW_TABLE may only be specified once each and only for AFTER triggers.

  3. FOR EACH STATEMENT may not be specified for BEFORE triggers.

Description

trigger-name
Names the trigger. The name, including the implicit or explicit schema name must not identify a trigger already described in the catalog (SQLSTATE 42710). If a two part name is specified, the schema name cannot begin with "SYS" (SQLSTATE 42939).

NO CASCADE BEFORE
Specifies that the associated triggered action is to be applied before any changes caused by the actual update of the subject table are applied to the database. It also specifies that the triggered action of the trigger will not cause other triggers to be activated.

AFTER
Specifies that the associated triggered action is to be applied after the changes caused by the actual update of the subject table are applied to the database.

INSERT
Specifies that the triggered action associated with the trigger is to be executed whenever an INSERT operation is applied to the designated base table.

DELETE
Specifies that the triggered action associated with the trigger is to be executed whenever a DELETE operation is applied to the designated base table.

UPDATE
Specifies that the triggered action associated with the trigger is to be executed whenever an UPDATE operation is applied to the designated base table subject to the columns specified or implied.

If the optional column-name list is not specified, every column of the table is implied. Therefore, omission of the column-name list implies that the trigger will be activated by the update of any column of the table.

OF column-name,...
Each column-name specified must be a column of the base table (SQLSTATE 42703). If the trigger is a BEFORE trigger, the column-name specified may not be a generated column other than the identity column (SQLSTATE 42989). No column-name shall appear more than once in the column-name list (SQLSTATE 42711). The trigger will only be activated by the update of a column identified in the column-name list.

ON table-name
Designates the subject table of the trigger definition. The name must specify a base table or an alias that resolves to a base table (SQLSTATE 42809). The name must not specify a catalog table (SQLSTATE 42832), a summary table (SQLSTATE 42997), a declared temporary table (SQLSTATE 42995), or a nickname (SQLSTATE 42809).

REFERENCING
Specifies the correlation names for the transition variables 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 names 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 AS correlation-name
Specifies a correlation name which identifies the row state prior to the triggering SQL operation.

NEW AS correlation-name
Specifies a correlation name which identifies the row state as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed.

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 identifier
Specifies a temporary table name which identifies the set of affected rows prior to the triggering SQL operation.

NEW_TABLE AS identifier
Specifies a temporary table name which identifies the affected rows as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed.

The following rules apply to the REFERENCING clause:

FOR EACH ROW
Specifies that the triggered action is to be applied once for each row of the subject table that is affected by the triggering SQL operation.

FOR EACH STATEMENT
Specifies that the triggered action is to be applied only once for the whole statement. This type of trigger granularity cannot be specified for a BEFORE trigger (SQLSTATE 42613). If specified, an UPDATE or DELETE trigger is activated even when no rows are affected by the triggering UPDATE or DELETE statement.

MODE DB2SQL
This clause is used to specify the mode of triggers. This is the only valid mode currently supported.

triggered-action
Specifies the action to be performed when a trigger is activated. A triggered-action is composed of one or several triggered-SQL-statements and by an optional condition for the execution of the triggered-SQL-statements. If there is more than one triggered-SQL-statement in the triggered-action for a given trigger, they must be enclosed within the BEGIN ATOMIC and END keywords, separated by a semi-colon, 83 and are executed in the order they are specified.

WHEN (search-condition)
Specifies a condition that is true, false, or unknown. The search-condition provides a capability to determine whether or not a certain triggered action should be executed.

The associated action is performed only if the specified search condition evaluates as true. If the WHEN clause is omitted, the associated triggered-SQL-statements are always performed.

triggered-SQL-statement
If the trigger is a BEFORE trigger, then a triggered SQL statement must be one of the following (SQLSTATE 42987):
  • a fullselect 84
  • a SET transition-variable SQL statement.
  • a SIGNAL SQLSTATE statement

If the trigger is an AFTER trigger, then a triggered SQL statement must be one of the following (SQLSTATE 42987):

  • an INSERT SQL statement
  • a searched UPDATE SQL statement
  • a searched DELETE SQL statement
  • a SIGNAL SQLSTATE statement
  • a fullselect 84

The triggered-SQL-statement cannot reference an undefined transition variable (SQLSTATE 42703) or a declared temporary table (SQLSTATE 42995).

The triggered-SQL-statement in a BEFORE trigger cannot reference a summary table defined with REFRESH IMMEDIATE (SQLSTATE 42997).

The triggered-SQL-statement in a BEFORE trigger cannot reference a generated column, other than the identity column, in the new transition variable (SQLSTATE 42989).

Notes

Examples

Example 1:  Create two triggers that will result in the automatic tracking of the number of employees a company manages. The triggers will interact with the following tables:

EMPLOYEE table with these columns: ID, NAME, ADDRESS, and POSITION.
COMPANY_STATS table with these columns: NBEMP, NBPRODUCT, and REVENUE.

The first trigger 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: 

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

The second trigger decrements the number of employees each time an employee leaves the company; that is, each time a row is deleted from the table EMPLOYEE: 

   CREATE TRIGGER FORMER_EMP
      AFTER DELETE ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1

Example 2:  Create a trigger that ensures that whenever a parts record is updated, the following check and (if necessary) action is taken:

If the on-hand quantity is less than 10% of the maximum stocked quantity, then issue a shipping request ordering the number of items for the affected part to be equal to the maximum stocked quantity minus the on-hand quantity.

The trigger will interact with the PARTS table with these columns: PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED, and PRICE.

ISSUE_SHIP_REQUEST is a user-defined function that sends an order form for additional parts to the appropriate company. 

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

Example 3:  Create a trigger that will cause an error when an update occurs that would result in a salary increase greater than ten percent of the current salary. 

   CREATE TRIGGER RAISE_LIMIT
     AFTER UPDATE OF SALARY ON EMPLOYEE
     REFERENCING NEW AS N OLD AS O
     FOR EACH ROW MODE DB2SQL
     WHEN (N.SALARY > 1.1 * O.SALARY)
            SIGNAL SQLSTATE '75000' ('Salary increase>10%')

Example 4:  Consider an application which records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY. 

Tables: CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
        QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)

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

  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.

CREATE TRIGGER statements that accomplish this are as follows.

Footnotes:

83
When using this form in the Command Line Processor, the statement terminating character cannot be the semi-colon. See the Command Reference for information on specifying an alternative terminating character.

84
A common-table-expression may precede a fullselect.

[ Top of Page | Previous Page | Next Page ]