15.00 - Row and Statement Triggers - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Row and Statement Triggers

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 (External Form)/ REPLACE PROCEDURE (External Form)” on page 422 and “CREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE (SQL Form)” on page 455).

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” on page 727.

    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