SQL for triggered actions: summary

The SQL for a triggered action can start with a WHEN clause that specifies the condition under which the action is to occur.

If you want the action to occur before a trigger event, the code must include one of the following statements:

• A SET transition-variable statement.

• A SIGNAL SQLSTATE statement.

• A fullselect. A common-table-expression can precede the fullselect.

If you want the action to occur after a trigger event, the code must include one of the following statements:

• An INSERT statement.

• A searched UPDATE statement.

• A searched DELETE statement.

• A SIGNAL SQLSTATE statement.

• A fullselect. A common-table-expression can precede the fullselect.

Example: A trigger called REORDER 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.

In the following definition of REORDER:

• PARTS is the table on which REORDER is defined.

• MAX_STOCKED, ON_HAND, and PARTNO are columns in PARTS.

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

• The code for the triggered action begins with the keyword WHEN and ends with the keyword END.

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

For more examples of SQL for triggered actions, see online help for correlation names and temporary tables. See also "CREATE TRIGGER" in the SQL Reference.

[ Top of Page | Table of Contents | Glossary | Index ]

[ DB2 List of Books | Search the DB2 Books ]