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

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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.