How to Reference Old Values and New Values in SQL

By Allen G. Taylor

One part of the SQL CREATE TRIGGER syntax that you can use is the optional REFERENCING old_or_new_value_alias_list phrase. It enables you to create an alias or correlation name that references values in the trigger’s subject table. After you create a correlation name for new values or an alias for new table contents, you can then reference the values that will exist after an INSERT or UPDATE operation.

In a similar way, after you create a correlation name for old values or an alias for old table contents, you can then reference the values that existed in the subject table before an UPDATE or DELETE operation.

The old_or_new_values_alias_list in the CREATE TRIGGER syntax can be one or more of the following phrases:

OLD [ ROW ] [ AS ] <old values correlation name>

or

NEW [ ROW ] [ AS ] <new values correlation name>

or

OLD TABLE [ AS ] <old values table alias>

or

NEW TABLE [ AS ] <new values table alias>

The table aliases are identifiers for transition tables, which are not persistent, but which exist only to facilitate the referencing operation. As you would expect, NEW ROW and NEW TABLE cannot be specified for a DELETE trigger, and OLD ROW as well as OLD TABLE cannot be specified for an INSERT trigger.

After you delete a row or table, there is no new value. Similarly, OLD ROW and OLD TABLE cannot be specified for an INSERT trigger. There are no old values to reference.

In a row-level trigger, you can use an old value correlation name to reference the values in the row being modified or deleted by the triggering SQL statement as that row existed before the statement modified or deleted it. Similarly, an old value table alias is what you use to access the values in the entire table as they existed before the triggering SQL statement’s action took effect.

You may not specify either OLD TABLE or NEW TABLE with a BEFORE trigger. The transition tables created by the OLD TABLE or NEW TABLE keyword are too likely to be affected by the actions caused by the triggered SQL statement. To eliminate this potential problem, using OLD TABLE and NEW TABLE with a BEFORE trigger is prohibited.