REFERENCING Clause | CREATE/REPLACE TRIGGER | Teradata Vantage - 17.10 - REFERENCING Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
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.