DB2 10.5 for Linux, UNIX, and Windows

CREATE TRIGGER statement

The CREATE TRIGGER statement defines a trigger in the database. Triggers can be created to support general forms of integrity or business rules. A trigger defines a set of actions that are executed with, or triggered by, an INSERT, UPDATE, or DELETE statement.

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 authorities:
  • 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 authorities, 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).

If the SECURED option is specified, the privileges held by the authorization ID of the statement must additionally include SECADM or CREATE_SECURE_OBJECT authority (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-'       

                                 .-NOT SECURED-.   
>--+-FOR EACH ROW-------------+--+-------------+---------------->
   |  (3)                     |  '-SECURED-----'   
   '-------FOR EACH STATEMENT-'                    

>--| triggered-action |----------------------------------------><

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--------------------------------------------+   
   '-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).
NOT SECURED or SECURED
Specifies whether the trigger is considered secure. The default is NOT SECURED.
NOT SECURED
Specifies the trigger is considered not secure.
SECURED
Specifies the trigger is considered secure. SECURED must be specified for a trigger whose subject table is a table on which row level or column level access control has been activated (SQLSTATE 428H8). Similarly, SECURED must be specified for a trigger that is created on a view and one or more of the underlying tables in that view definition has row level or column level access control activated (SQLSTATE 428H8).
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 before 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 before the triggering SQL operation. If the trigger event is INSERT, the temporary table is empty.
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. If the trigger event is DELETE, the temporary table is empty.
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.

Trigger event predicates can be used anywhere in the triggered action of a CREATE TRIGGER statement that uses a compound SQL (compiled) statement as the SQL-procedure-statement.

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). or the start and end columns of the BUSINESS_TIME period (SQLSTATE 42808).

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

A triggered action cannot include an operation that does an insert or update to a column-organized table.

Notes

Examples

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