Accessing old and new column values in triggers using transition variables

When you implement a FOR EACH ROW trigger, it might 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.

About this task

A FOR EACH ROW trigger can 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 AS 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 AS 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.

Example

Consider the following example:

    CREATE TRIGGER REORDER 
      AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS 
      REFERENCING NEW AS N_ROW 
      FOR EACH ROW
      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

What to do next

Based on the definition of the OLD and NEW transition variables given previously, 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. Instead, refer to the set of new and old rows by using the OLD TABLE and NEW TABLE clauses of the CREATE TRIGGER statement. For more information about these clauses, see the CREATE TRIGGER statement.