Application Development Guide

Transition Variables

When you carry out a FOR EACH ROW trigger, it may be necessary to refer to the value of columns of the row in the set of affected rows, for which the trigger is currently executing. Note that to refer to columns in tables in the database (including the subject table), you can use regular SELECT statements. A FOR EACH ROW trigger may refer to the columns of the row for which it is currently executing by using two transition variables that you can specify in the REFERENCING clause of a CREATE TRIGGER statement. There are two kinds of transition variables, which are specified as OLD and NEW, together with a correlation-name. They have the following semantics:

OLD correlation-name: Specifies a correlation name which captures the original state of the row, that is, before the triggered action is applied to the database.
NEW correlation-name: Specifies a correlation name which captures the value that is, or was, used to update the row in the database when the triggered action is applied to the database.

Consider the following example:

    CREATE TRIGGER REORDER 
      AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS 
      REFERENCING NEW AS N_ROW 
      FOR EACH ROW MODE DB2SQL 
      WHEN (N_ROW.ON_HAND < 0.10 * N_ROW.MAX_STOCKED 
      AND N_ROW.ORDER_PENDING = 'N') 
      BEGIN ATOMIC 
        VALUES(ISSUE_SHIP_REQUEST(N_ROW.MAX_STOCKED - 
                                  N_ROW.ON_HAND, 
                                  N_ROW.PARTNO)); 
        UPDATE PARTS SET PARTS.ORDER_PENDING = 'Y' 
        WHERE PARTS.PARTNO = N_ROW.PARTNO; 
      END

Based on the definition of the OLD and NEW transition variables given above, it is clear that not every transition variable can be defined for every trigger. Transition variables can be defined depending on the kind of trigger event:

UPDATE: An UPDATE trigger can refer to both OLD and NEW transition variables.
INSERT: An INSERT trigger can only refer to a NEW transition variable because before the activation of the INSERT operation, the affected row does not exist in the database. That is, there is no original state of the row that would define old values before the triggered action is applied to the database.
DELETE: A DELETE trigger can only refer to an OLD transition variable because there are no new values specified in the delete operation.

Note:

Transition variables can only be specified for FOR EACH ROW triggers. In a FOR EACH STATEMENT trigger, a reference to a transition variable is not sufficient to specify to which of the several rows in the set of affected rows the transition variable is referring.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]