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