Using FOR EACH ROW Triggered Actions

A FOR EACH ROW triggered action executes once for each row that the triggering statement affects. For example, if the triggering statement has the following syntax, a FOR EACH ROW triggered action executes once for each row in the items table in which the manacled column has a value of 'KAR':

UPDATE items SET quantity = quantity * 2 
   WHERE manu_code = 'KAR'

If the triggering statement does not process any rows, a FOR EACH ROW triggered action does not execute.

If the triggering statement is a SELECT statement (the trigger is a select trigger), the triggered actions execute after all processing on the retrieved row is complete. However, the triggered actions might not execute immediately; a FOR EACH ROW action executes for every instance of a row that is returned to the user. For example, in a SELECT statement with an ORDER BY clause, all rows must be qualified against the WHERE clause before they are sorted and returned.

Using the REFERENCING Clause

When you create a FOR EACH ROW triggered action, you must usually indicate in the triggered action statements whether you are referring to the value of a column before or after the effect of the triggering statement. For example, imagine that you want to track updates to the quantity column of the items table. To do this, create the following table to record the activity:

CREATE TABLE log_record
   (item_num     SMALLINT,
   ord_num       INTEGER,
   username      CHARACTER(8),
   update_time   DATETIME YEAR TO MINUTE,
   old_qty       SMALLINT,
   new_qty       SMALLINT);

To supply values for the old_qty and new_qty columns in this table, you must be able to refer to the old and new values of quantity in the items table; that is, the values before and after the effect of the triggering statement. The REFERENCING clause enables you to do this.

The REFERENCING clause lets you create two prefixes that you can combine with a column name, one to reference the old value of the column, and one to reference its new value. These prefixes are called correlation names. You can create one or both correlation names, depending on your requirements. You indicate which one you are creating with the keywords OLD and NEW. The following REFERENCING clause creates the correlation names pre_upd and post_upd to refer to the old and new values in a row:

REFERENCING OLD AS pre_upd NEW AS post_upd

The following triggered action creates a row in log_record when quantity is updated in a row of the items table. The INSERT statement refers to the old values of the item_num and order_num columns and to both the old and new values of the quantity column.

FOR EACH ROW(INSERT INTO log_record
  VALUES (pre_upd.item_num, pre_upd.order_num, USER,
          CURRENT, pre_upd.quantity, post_upd.quantity));

The correlation names defined in the REFERENCING clause apply to all rows that the triggering statement affects.

Using the WHEN Condition

As an option, you can precede a triggered action with a WHEN clause to make the action dependent on the outcome of a test. The WHEN clause consists of the keyword WHEN followed by the condition statement given in parentheses. In the CREATE TRIGGER statement, the WHEN clause follows the keywords BEFORE, AFTER, or FOR EACH ROW and precedes the triggered-action list.

When a WHEN condition is present, if it evaluates to true, the triggered actions execute in the order in which they appear. If the WHEN condition evaluates to false or unknown, the actions in the triggered-action list do not execute. If the trigger specifies FOR EACH ROW, the condition is evaluated for each row also.

In the following trigger example, the triggered action executes only if the condition in the WHEN clause is true; that is, if the post-update unit price is greater than two times the pre-update unit price:

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.manu_code, pre.unit_price,
           post.unit_price, CURRENT))

For more information on the WHEN condition, see the CREATE TRIGGER statement in the IBM Informix: Guide to SQL Syntax.