Row and Statement Triggers | CREATE/REPLACE TRIGGER | Teradata Vantage - Row and Statement 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™

Triggers are of two mutually exclusive types: row and statement. You cannot combine row and statement operations within a single trigger definition.

Both row and statement triggers can call stored procedures (see CREATE PROCEDURE and REPLACE PROCEDURE (External Form) and CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)).

The following table summarizes the key differences between row and statement triggers.

A row trigger … A statement trigger …
fires once for each row changed by the triggering statement.

The process of firing a trigger includes testing WHEN conditions for their truth value and not firing if the specified condition evaluates to FALSE.

There is no limit on the number of times a row trigger fires.

fires only once.

Statement triggers cannot access changed rows.

does not fire if no row is modified by the triggering statement. fires even if no row is modified by the triggering statement.
can be either a BEFORE or AFTER trigger. can only be an AFTER trigger.
can access any OLD values, NEW values; or any OLD TABLE, OLD_TABLE, NEW TABLE, or NEW_TABLE transition tables that make sense by means of the REFERENCING clause.

For example, it makes no sense to perform an INSERT with an OLD [ROW] transition variable or an OLD TABLE transition table because there can be no pre-existing variable or table (see the next table for a complete list of valid and non-valid transition variables and tables).

See the table beginning on the following page for details of which of these transitions are valid for a given triggered action statement.

cannot use the REFERENCING options of OLD and NEW transition table names, only the OLD TABLE, OLD_TABLE, NEW TABLE, and NEW_TABLE transition table names.

See the table beginning on the following page for details of which of these transitions are valid for a given triggered action statement.

cannot specify OLD_NEW_TABLE transition tables.

There is no analog of OLD_NEW_TABLE transition tables for row triggers.

can specify OLD_NEW_TABLE transition tables.

If your application can be written using either a row trigger or a statement trigger, you can almost always achieve better performance using the statement trigger because it fires only once, while the equivalent row trigger fires once for each row that the triggering statement updates.

The following table summarizes the valid combinations of trigger types, trigger activation times, update operations, and transition variables and tables:

Trigger Type Activation Time Update Operation Valid Transitions
ROW BEFORE DELETE OLD [ROW]
INSERT NEW [ROW]
MERGE INSERT NEW [ROW]
MERGE UPDATE
  • OLD [ROW]
  • NEW [ROW]
UPDATE
  • OLD [ROW]
  • NEW [ROW]
AFTER DELETE
  • OLD [ROW]
  • OLD_TABLE
  • OLD TABLE
INSERT
  • NEW [ROW]
  • NEW_TABLE
  • NEW TABLE
MERGE INSERT
  • NEW [ROW]
  • NEW_TABLE
  • NEW TABLE
MERGE UPDATE
  • NEW [ROW]
  • NEW_TABLE
  • NEW TABLE
  • OLD [ROW]
  • OLD_TABLE
  • OLD TABLE
UPDATE
  • NEW [ROW]
  • NEW_TABLE
  • NEW TABLE
  • OLD [ROW]
  • OLD_TABLE
  • OLD TABLE
STATEMENT BEFORE Not applicable.

BEFORE STATEMENT triggers are not defined by the ANSI SQL:2011 standard, nor are they supported by Teradata.

See Unsupported Trigger Features.

AFTER DELETE
  • OLD_TABLE
  • OLD TABLE
INSERT
  • NEW_TABLE
  • NEW TABLE
MERGE INSERT
  • NEW_TABLE
  • NEW TABLE
MERGE UPDATE
  • NEW_TABLE
  • NEW TABLE
  • OLD_TABLE
  • OLD TABLE
UPDATE
  • NEW_TABLE
  • NEW TABLE
  • OLD_TABLE
  • OLD TABLE
  • OLD_NEW_TABLE