Using Row or Statement Triggers | Teradata Vantage - When To Use Row Triggers - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

Row triggers are a more natural choice than statement triggers for various reasons, including performance and ease of operation.

When a data-changing statement with one or more triggers is executed, the system creates one (for delete or insert operations) or more (for update operations) transition tables before the statement executes.

For row triggers, the system scans the transition table set, accessing one row at a time. You cannot specify the ordering of the transition tables, so do not write triggered actions with outcomes that are intended to differ depending on the order of rows in the transition table.

The volume of work required to execute a row trigger can vary, depending on the information available to the triggered action, as described in the following table:

Source of data for triggered action Comment
The specific rows being processed
  • For a DELETE or UPDATE, the old value row is accessible if the REFERENCING clause provides a correlation name for the OLD row.
  • For an INSERT or UPDATE, the new value is accessible if the REFERENCING clause provides a correlation name for the NEW row.
  • Fields from these rows can be accessed as scalar values by means of the specified WHEN condition and by triggered action statements.
The transition tables composed of all the rows changed by the triggering statement
  • For a DELETE or UPDATE, the old rows are accessible as a temporary table if the REFERENCING clause provides a correlation name for the OLD TABLE.
  • For an INSERT or UPDATE, the new rows are accessible if the REFERENCING clause provides a correlation name for the NEW TABLE.
  • Columns in these tables are accessible by means of the specified WHEN condition and by triggered action statements as outer table references.

    The typical use of such references is through aggregate statements accessing the correlation names and providing scalar values for use in the WHEN condition and triggered action statements.

The full set of rows in the table being changed This behavior is similar to the behavior described in the transition tables row, but there is no syntax to indicate OLD or NEW values.
  • For BEFORE triggers, the rows accessed are the rows of the triggering table before any changes have been made to it.
  • For AFTER triggers, the rows accessed are the rows of the triggering table after changes have been made to it.

This information comes from the triggering table and does not require a REFERENCING clause to be accessed.

For these kinds of outer references, the typical use of direct references to the triggering table is by means of the following methods:
  • Aggregation statements accessing columns using the table name and providing scalar values for use in the WHEN condition.
  • Triggered action statements.