Referencing old and new table result sets using transition tables

In both FOR EACH ROW and FOR EACH STATEMENT triggers, it might be necessary to refer to the whole set of affected rows. This is necessary, for example, if the trigger body needs to apply aggregations over the set of affected rows (for example, MAX, MIN, or AVG of some column values).

About this task

A trigger can refer to the set of affected rows by using two transition tables that can be specified in the REFERENCING clause of a CREATE TRIGGER statement. Just like the transition variables, there are two kinds of transition tables, which are specified as OLD_TABLE and NEW_TABLE together with a table-name, with the following semantics:

OLD_TABLE AS table-name: Specifies the name of the table which captures the original state of the set of affected rows (that is, before the triggering SQL operation is applied to the database).
NEW_TABLE AS table-name: Specifies the name of the table which captures the value that is used to update the rows in the database when the triggered action is applied to the database.

Example

For example:

    CREATE TRIGGER REORDER 
      AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS 
      REFERENCING NEW_TABLE AS N_TABLE 
      NEW AS N_ROW 
      FOR EACH ROW 
      WHEN ((SELECT AVG (ON_HAND) FROM N_TABLE) > 35) 
      BEGIN ATOMIC 
        VALUES(INFORM_SUPERVISOR(N_ROW.PARTNO, 
                                 N_ROW.MAX_STOCKED, 
                                 N_ROW.ON_HAND)); 
      END

Note that NEW_TABLE always has the full set of updated rows, even on a FOR EACH ROW trigger. When a trigger acts on the table on which the trigger is defined, NEW_TABLE contains the changed rows from the statement that activated the trigger. However, NEW_TABLE does not contain the changed rows that were caused by statements within the trigger, as that would cause a separate activation of the trigger.

What to do next

The transition tables are read-only. The same rules that define the kinds of transition variables that can be defined for which trigger event, apply for transition tables:

UPDATE: An UPDATE trigger can refer to both OLD_TABLE and NEW_TABLE transition tables.
INSERT: An INSERT trigger can only refer to a NEW_TABLE transition table because before the activation of the INSERT operation the affected rows do not exist in the database. That is, there is no original state of the rows that defines old values before the triggered action is applied to the database.
DELETE: A DELETE trigger can only refer to an OLD_TABLE transition table because there are no new values specified in the delete operation.

Note: It is important to observe that transition tables can be specified for both granularities of AFTER triggers: FOR EACH ROW and FOR EACH STATEMENT.

The scope of the OLD_TABLE and NEW_TABLE table-name is the trigger body. In this scope, this name takes precedence over the name of any other table with the same unqualified table-name that might exist in the schema. Therefore, if the OLD_TABLE or NEW_TABLE table-name is for example, X, a reference to X (that is, an unqualified X) in the FROM clause of a SELECT statement will always refer to the transition table even if there is a table named X in the in the schema of the trigger creator. In this case, the user has to make use of the fully qualified name in order to refer to the table X in the schema.