16.20 - REFERENCING Clause - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

The REFERENCING clause allows the WHEN condition and triggered actions of a trigger to reference the set of rows in the transition table set. This indirect access to the transition table rows is useful for making comparisons between OLD and NEW rows in the subject table or for use in the triggered action.

The references are to transient and virtual tables, which can include values from the subject table either before (OLD ROW or OLD TABLE), after (NEW ROW or NEW TABLE), or before and after (OLD_NEW_TABLE) the data-changing statement. Only AFTER UPDATE statement triggers can produce OLD_NEW_TABLE transition tables.

These are distinct from direct references to the subject table. Triggers cannot make direct comparisons between before and after rows of a subject table.

A reference to the subject table in a triggered SQL statement is called an outer reference. Outer references occur when the triggered SQL statement of a WHEN clause or the WHEN clause itself refers to a column or row in the subject table.