Triggered-Action List

Triggered Action List:
 
   .-,--------------------------------------------------------------------------------------------.
   |                                        .-,----------------------------------------------.    |
   V                                        V                        (2)                     |    |
|----+--------------------------------+--(----+-| INSERT Statement |-----------------------+-+--)-+--|
     |                        (1)     |       |                      (3)                   |
     '-WHEN--(--| Condition |-------)-'       +-| DELETE Statement |-----------------------+
                                              |                      (4)                   |
                                              +-| UPDATE Statement |-----------------------+
                                              |                                 (5)        |
                                              +-| EXECUTE PROCEDURE Statement |------------+
                                              |  (6)                                  (7)  |
                                              '--------| EXECUTE FUNCTION Statement |------'
 

1. See page Condition
2. See page INSERT
3. See page DELETE
4. See page UPDATE
5. See page EXECUTE PROCEDURE
6. Dynamic Server only
7. See page EXECUTE FUNCTION

For a trigger on a table, the trigger action consists of an optional WHEN condition and the action statements. You can specify a triggered-action list for each WHEN clause, or you can specify a single list (of one or more trigger actions) if you include no WHEN clause.

Database objects that are referenced explicitly in the trigger action or in the definition of the trigger event, such as tables, columns, and UDRs, must exist when the CREATE TRIGGER statement defines the new trigger.

WHEN Condition

The WHEN condition makes the triggered action dependent on the outcome of a test. When you include a WHEN condition in a triggered action, the statements in the triggered action list execute only if the condition evaluates to true. If the WHEN condition evaluates to false or unknown, then the statements in the triggered action list are not executed.

If the triggered action is in a FOR EACH ROW section, its condition is evaluated for each row. For example, the triggered action in the following trigger executes only if the condition in the WHEN clause is true:

CREATE TRIGGER up_price
   UPDATE OF unit_price ON stock
   REFERENCING OLD AS pre NEW AS post
   FOR EACH ROW WHEN(post.unit_price > pre.unit_price * 2)
   (INSERT INTO warn_tab VALUES(pre.stock_num, pre.order_num, 
      pre.unit_price, post.unit_price, CURRENT))

An SPL routine that executes inside the WHEN condition carries the same restrictions as a UDR that is called in a data manipulation statement. That is, the SPL routine cannot contain certain SQL statements. For information on which statements are restricted, see Restrictions on SPL Routines in Data-Manipulation Statements.

Action Statements

The triggered-action statements can be INSERT, DELETE, UPDATE, EXECUTE FUNCTION, or EXECUTE PROCEDURE statements. If the action list contains multiple statements, and the WHEN condition is satisfied (or is absent), then these statements execute in the order in which they appear in the list.

UDRs as Triggered Actions

User-defined functions and procedures can be triggered actions.

In Dynamic Server, you can use the EXECUTE FUNCTION statement to call any user-defined function. Use the EXECUTE PROCEDURE statement to call any user-defined procedure.

In Extended Parallel Server, you can use the EXECUTE PROCEDURE statement to execute any SPL routine.

For restrictions on using SPL routines as triggered actions, see Rules for SPL Routines and Triggers and SPL Routines.

Achieving a Consistent Result

To guarantee that the triggering statement returns the same result with and without the triggered actions, make sure that the triggered actions in the BEFORE and FOR EACH ROW sections do not modify any table referenced in the following clauses:

• WHERE clause
• SET clause in the UPDATE statement
• SELECT clause
• EXECUTE PROCEDURE clause or EXECUTE FUNCTION clause in a multiple-row INSERT statement.

Using Reserved Words

If you use the INSERT, DELETE, UPDATE, or EXECUTE reserved words as an identifier in any of the following clauses inside a triggered action list, you must qualify them by the owner name, the table name, or both:

• FROM clause of a SELECT statement
• INTO clause of the EXECUTE PROCEDURE or EXECUTE FUNCTION statement
• GROUP BY clause
• SET clause of the UPDATE statement.

You get a syntax error if these keywords are not qualified when you use these clauses inside a triggered action.

If you use the keyword as a column name, it must be qualified by the table name; for example, table.update. If both the table name and the column name are keywords, they must be qualified by the owner name (for example, owner.insert.update). If the owner name, table name, and column name are all keywords, the owner name must be in quotes; for example, 'delete'.insert.update. (These are general rules regarding reserved words as identifiers, rather than special cases for triggers. Your code will be easier to read and to maintain if you avoid using the keywords of SQL as identifiers.)

The only exception is when these keywords are the first table or column name in the list, and you do not have to qualify them. For example, delete in the following statement does not need to be qualified because it is the first column listed in the INTO clause:

CREATE TRIGGER t1 UPDATE OF b ON tab1
   FOR EACH ROW (EXECUTE PROCEDURE p2() INTO delete, d)

The following statements show examples in which you must qualify the column name or the table name:

• FROM clause of a SELECT statement

  CREATE TRIGGER t1 INSERT ON tab1
     BEFORE (INSERT INTO tab2 SELECT * FROM tab3, 'owner1'.update)

• INTO clause of an EXECUTE PROCEDURE statement

  CREATE TRIGGER t3 UPDATE OF b ON tab1
     FOR EACH ROW (EXECUTE PROCEDURE p2() INTO
     d, tab1.delete)

  In Dynamic Server, an INSTEAD OF trigger on a view cannot include the EXECUTE PROCEDURE INTO statement among its triggered actions.

• GROUP BY clause of a SELECT statement

  CREATE TRIGGER t4 DELETE ON tab1
     BEFORE (INSERT INTO tab3 SELECT deptno, SUM(exp)
     FROM budget GROUP BY deptno, budget.update)

• SET clause of an UPDATE statement

  CREATE TRIGGER t2 UPDATE OF a ON tab1
     BEFORE (UPDATE tab2 SET a = 10, tab2.insert = 5)