DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE TRIGGER statement

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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:
  • ALTER privilege on the table on which the BEFORE or AFTER trigger is defined
  • CONTROL privilege on the view on which the INSTEAD OF TRIGGER is defined
  • Owner of the view on which the INSTEAD OF trigger is defined
  • ALTERIN privilege on the schema of the table or view on which the trigger is defined
  • DBADM authority
and one of:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the trigger does not exist
  • CREATEIN privilege on the schema, if the schema name of the trigger refers to an existing schema
  • DBADM authority
If the authorization ID of the statement does not have DATAACCESS authority, the privileges (excluding group privileges) held by the authorization ID of the statement must include all of the following, as long as the trigger exists:
  • On the table on which the trigger is defined, if any transition variables or tables are specified:
    • SELECT privilege on the table on which the trigger is defined, if any transition variables or tables are specified
    • CONTROL privilege on the table on which the trigger is defined, if any transition variables or tables are specified
    • DATAACCESS authority
  • On any table or view referenced in the triggered action condition:
    • SELECT privilege on any table or view referenced in the triggered action condition
    • CONTROL privilege on any table or view referenced in the triggered action condition
    • DATAACCESS authority
  • Necessary privileges to invoke the triggered SQL statements specified.

Group privileges are not considered for any table or view specified in the CREATE TRIGGER statement.

To replace an existing trigger, the authorization ID of the statement must be the owner of the existing trigger (SQLSTATE 42501).

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE--+------------+--TRIGGER--trigger-name---------------->
           '-OR REPLACE-'                          

     .-NO CASCADE-.                                
>--+-+------------+--BEFORE-+--| trigger-event |---------------->
   +-AFTER------------------+                      
   '-INSTEAD OF-------------'                      

>--ON--+-table-name-+------------------------------------------->
       '-view-name--'   

>--+----------------------------------------------------------------+-->
   |              .-----------------------------------------------. |   
   |              V  (1)   (2)         .-AS-.                     | |   
   '-REFERENCING----------------+-OLD--+----+--correlation-name-+-+-'   
                                |      .-AS-.                   |       
                                +-NEW--+----+--correlation-name-+       
                                |            .-AS-.             |       
                                +-OLD TABLE--+----+--identifier-+       
                                |            .-AS-.             |       
                                '-NEW TABLE--+----+--identifier-'       

>--+-FOR EACH ROW-------------+--| triggered-action |----------><
   |  (3)                     |                         
   '-------FOR EACH STATEMENT-'                         

trigger-event

   .-OR--------------------------------------.   
   V                                     (4) |   
|----+-INSERT--------------------------+-----+------------------|
     +-DELETE--------------------------+         
     '-UPDATE--+---------------------+-'         
               |     .-,-----------. |           
               |     V             | |           
               '-OF----column-name-+-'           

triggered-action

|--+------------------------------------+----------------------->
   |  (5)                               |   
   '-------WHEN--(--search-condition--)-'   

>--+--------+--| SQL-procedure-statement |----------------------|
   '-label:-'                                

SQL-procedure-statement

|--+-CALL----------------------------------------------+--------|
   |                         (6)                       |   
   +-Compound SQL (compiled)---------------------------+   
   +-Compound SQL (inlined)----------------------------+   
   +-FOR-----------------------------------------------+   
   +-+-----------------------------------+--fullselect-+   
   | |       .-,-----------------------. |             |   
   | |       V                         | |             |   
   | '-WITH----common-table-expression-+-'             |   
   +-GET DIAGNOSTICS-----------------------------------+   
   +-IF------------------------------------------------+   
   +-INSERT--------------------------------------------+   
   +-ITERATE-------------------------------------------+   
   +-LEAVE---------------------------------------------+   
   +-MERGE---------------------------------------------+   
   +-searched-delete-----------------------------------+   
   +-searched-update-----------------------------------+   
   +-SET Variable--------------------------------------+   
   +-SIGNAL--------------------------------------------+   
   +-FOR-----------------------------------------------+   
   '-WHILE---------------------------------------------'   
Notes:
  1. OLD and NEW can only be specified once each.
  2. OLD TABLE and NEW TABLE can only be specified once each, and only for AFTER triggers or INSTEAD OF triggers.
  3. FOR EACH STATEMENT may not be specified for BEFORE triggers or INSTEAD OF triggers.
  4. A trigger event must not be specified more than once for the same operation. For example, INSERT OR DELETE is allowed, but INSERT OR INSERT is not allowed.
  5. WHEN condition may not be specified for INSTEAD OF triggers.
  6. A compound SQL (compiled) statement cannot be specified if the trigger definition includes a REFERENCING OLD TABLE clause or a REFERENCING NEW TABLE clause. A compound SQL (compiled) statement also cannot be specified for a trigger definition in a partitioned database environment.

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. This option is ignored if a definition for the trigger does not exist at the current server. This option can be specified only by the owner of the object.
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.
INSTEAD OF
Specifies that 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 (SQLSTATE 428FP).
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 subject view. Any combination of the events can be specified, but each event (INSERT, DELETE, and UPDATE) can only be specified once (SQLSTATE.42613). If multiple events are specified, the triggered action must be a compound SQL (compiled) statement (SQLSTATE 42601).
INSERT
Specifies that the triggered action associated with the trigger is to be executed whenever an INSERT operation is applied to the subject table or subject view.
DELETE
Specifies that the triggered action associated with the trigger is to be executed whenever a DELETE operation is applied to the subject table or subject view.
UPDATE
Specifies that the triggered action associated with the trigger is to be executed whenever an UPDATE operation is applied to the subject table or subject view, subject to the columns specified or implied.
If the optional column-name list is not specified, every column of the table or view 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 or view.
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 cannot be a generated column other than the identity column (SQLSTATE 42989). No column-name can appear more than once in the column-name list (SQLSTATE 42711). The trigger will only be activated by the update of a column that is identified in the column-name list. This clause cannot be specified for an INSTEAD OF trigger (SQLSTATE 42613).
ON
table-name
Designates the subject table of the BEFORE trigger or AFTER trigger definition. The name must specify a base table or an alias that resolves to a base table (SQLSTATE 42704 or 42809). The name must not specify a catalog table (SQLSTATE 42832), a materialized query table (SQLSTATE 42997), a created temporary table, a declared temporary table (SQLSTATE 42995), or a nickname (SQLSTATE 42809).
view-name
Designates the subject view of the INSTEAD OF trigger definition. The name must specify an untyped view or an alias that resolves to an untyped view with no columns of type XML (SQLSTATE 42704 or 42809). The name must not specify a catalog view (SQLSTATE 42832). The name must not specify a view that is defined using WITH CHECK OPTION (a symmetric view), or a view on which a symmetric view has been defined, directly or indirectly (SQLSTATE 428FQ).
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:
  • None of the OLD and NEW correlation names and the OLD TABLE and NEW TABLE names can be identical (SQLSTATE 42712).
  • Only one OLD and one NEW correlation-name may be specified for a trigger (SQLSTATE 42613).
  • Only one OLD TABLE and one NEW TABLE identifier may be specified for a trigger (SQLSTATE 42613).
  • OLD TABLE or NEW TABLE identifiers cannot be defined in a BEFORE trigger (SQLSTATE 42898).
  • A NEW transition variable can only be the target of an assignment in a BEFORE trigger. Otherwise, transition variables cannot be the target of an assignment (SQLSTATE 42703 or 42987).
  • OLD or NEW correlation names cannot be defined in a FOR EACH STATEMENT trigger (SQLSTATE 42899).
  • Transition tables cannot be modified (SQLSTATE 42807).
  • The total of the references to the transition table columns and transition variables in the triggered-action cannot exceed the limit for the number of columns in a table or the sum of their lengths cannot exceed the maximum length of a row in a table (SQLSTATE 54040).
  • The scope of each correlation-name and each identifier is the entire trigger definition.
  • If the triggered-action includes a compound SQL (compiled) statement:
    • OLD TABLE or NEW TABLE identifiers cannot be defined.
    • If the operation is a DELETE operation, OLD correlation-name captures the value of the deleted row. If it is an UPDATE operation, it captures the value of the row before the UPDATE operation. For an insert operation, OLD correlation-name captures null values for each column of a row.
    • For an insert operation or an update operation, the value of NEW captures the new state of the row as provided by the original operation and as modified by any BEFORE trigger that has executed to this point. For a delete operation, NEW correlation-name captures null values for each column of a row. In a BEFORE DELETE trigger, any non-null values assigned to the new transition variables persist only within the trigger where the assignment occurred.
  • If the triggered-action does not include a compound SQL (compiled) statement:
    • The OLD correlation-name and the OLD TABLE identifier can only be used if the trigger event is either a DELETE operation or an UPDATE operation (SQLSTATE 42898). If the operation is a DELETE operation, OLD correlation-name captures the value of the deleted row. If it is an UPDATE operation, it captures the value of the row before the UPDATE operation. The same applies to the OLD TABLE identifier and the set of affected rows.
    • The NEW correlation-name and the NEW TABLE identifier can only be used if the trigger event is either an INSERT operation or an UPDATE operation (SQLSTATE 42898). In both operations, the value of NEW captures the new state of the row as provided by the original operation and as modified by any BEFORE trigger that has executed to this point. The same applies to the NEW TABLE identifier and the set of affected rows.
FOR EACH ROW
Specifies that the triggered action is to be applied once for each row of the subject table or subject view 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 or an INSTEAD OF trigger (SQLSTATE 42613). If specified, an UPDATE or DELETE trigger is activated, even if no rows are affected by the triggering UPDATE or DELETE statement.
triggered-action
Specifies the action to be performed when a trigger is activated. A triggered action is composed of an SQL-procedure-statement and by an optional condition for the execution of the SQL-procedure-statement.

The trigger event predicates DELETING, INSERTING, and UPDATING can be specified anywhere inside the triggered action. If the triggered action does not contain any compound SQL (compiled), then no event predicate can be specified (42601).

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 SQL-procedure-statement is always performed.

The WHEN clause cannot be specified for INSTEAD OF triggers (SQLSTATE 42613).

A reference to a transition variable with an XML data type can be used only in a VALIDATED predicate.

label:
Specifies the label for an SQL procedure statement. The label must be unique within a list of SQL procedure statements, including any compound statements nested within the list. Note that compound statements that are not nested can use the same label. A list of SQL procedure statements is possible in a number of SQL control statements.

Only the FOR statement, WHILE statement, and the compound SQL statement can include a label.

SQL-procedure-statement
Specifies the SQL statement that is to be part of the triggered action. A searched update, searched delete, insert, or merge operation on nicknames inside compound SQL is not supported.

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.

The SQL-procedure-statement must not contain a statement that is not supported (SQLSTATE 42987).

The SQL-procedure-statement cannot reference an undefined transition variable (SQLSTATE 42703), a federated object (SQLSTATE 42997), or a declared temporary table (SQLSTATE 42995).

The SQL-procedure-statement in a BEFORE trigger cannot:
  • Contain any INSERT, DELETE, or UPDATE operations, nor invoke any routine defined with MODIFIES SQL DATA, if it is not a compound SQL (compiled).
  • Contain any DELETE or UPDATE operations on the trigger subject table, nor invoke any routine containing such operations, if it is a compound SQL (compiled).
  • Reference a materialized query table defined with REFRESH IMMEDIATE (SQLSTATE 42997)
  • 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
     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
     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
     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:  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.
   CREATE TRIGGER REORDER 
      AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS 
      REFERENCING NEW TABLE AS NTABLE
      FOR EACH STATEMENT
         BEGIN ATOMIC 
            SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
               FROM NTABLE 
            WHERE (ON_HAND < 0.10 * MAX_STOCKED); 
      END
Example 4: 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
     WHEN (N.SALARY > 1.1 * O.SALARY)
            SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'
Example 5: 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.
  • Trigger Definition to set the status: 
       CREATE TRIGGER STOCK_STATUS
     NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
     REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
     FOR EACH ROW
     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 Definition to record change in QUOTEHISTORY table: 
       CREATE TRIGGER RECORD_HISTORY
     AFTER UPDATE OF QUOTE ON CURRENTQUOTE
     REFERENCING NEW AS NEWQUOTE
     FOR EACH ROW
     BEGIN ATOMIC
       INSERT INTO QUOTEHISTORY
         VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
     END
Example 6:Create a trigger that overrides any changes to the location field in the employee record in the org table. This trigger would be useful if new employee records acquired when a smaller company was purchased are processed and the target location allocated to the employee is 'Toronto' and the new target location is 'Los Angeles'. The before trigger will ensure that regardless what value the application allocates for this field, that the final resultant value is 'Los Angeles'.
   CREATE TRIGGER LOCATION_TRIGGER
     NO CASCADE
      BEFORE UPDATE ON ORG
      REFERENCING
        OLD AS PRE
        NEW AS POST
      FOR EACH ROW
      WHEN (POST.LOCATION = 'Toronto')
         SET POST.LOCATION = 'Los Angeles';
      END
Example 7: Create a BEFORE trigger that automatically validates XML documents containing new product descriptions before they are inserted into the PRODUCT table of the SAMPLE database: 
   CREATE TRIGGER NEWPROD NO CASCADE BEFORE INSERT ON PRODUCT
      REFERENCING NEW AS N
      FOR EACH ROW
      BEGIN ATOMIC
         SET (N.DESCRIPTION) = XMLVALIDATE(N.DESCRIPTION
            ACCORDING TO XMLSCHEMA ID product);
      END
Related concepts:
Automatic revalidation of database objects
Related reference:
ADMIN_REVALIDATE_DB_OBJECTS procedure - Revalidate invalid database objects
Compound SQL (inlined) statement
REBIND_ROUTINE_PACKAGE procedure - rebind a package
WRAP function - Obfuscate a DDL statement