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

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.