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.