15.00 - When To Use Row Triggers - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

When To Use Row Triggers

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.