Syntax - Advanced SQL Engine - Teradata Database

{ CREATE | REPLACE } TRIGGER [ database_name. ] trigger_name
  [ ENABLED | DISABLED ]
  { BEFORE | AFTER }
  [ CURRENT | SEQUENCED | NONSEQUENCED ] VALIDTIME ] | NONTEMPORAL ]
  { INSERT |
    DELETE |
    UPDATE [ OF { column_name [,...] | ( column_name [,...] ) } ]
  }
  ON [ database_name. ] table_name [ ORDER integer ]
  [ REFERENCING reference_spec [...] ]
  [ FOR EACH { ROW | STATEMENT } ]
  [ WHEN ( search_condition ) ]
  { SQL_proc_spec | BEGIN ATOMIC SQL_proc_spec END } [;]

reference_spec

{ OLD [ROW] [AS] old_transition_variable_name |

  NEW [ROW] [AS] new_transition_variable_name |

  { OLD_TABLE | OLD TABLE } [AS] old_transition_table_name |

  { NEW_TABLE | NEW TABLE } [AS] new_transition_table_name |

  OLD_NEW_TABLE [AS] old_new_table_name ( old_value, new_value )
}

SQL_proc_spec

{ SQL_procedure_statement; [...] | ( SQL_procedure_statement; [...] ) }

database_name

Specifies an optional qualifier for trigger_name.

trigger_name

Specifies the name of the trigger to be created or replaced.

ENABLED

Specifies the keyword that enables a trigger to execute.

DISABLED

Specifies the keyword that disables a trigger from executing.

BEFORE

Specifies that the trigger performs before the triggering event, or triggering statement, is executed.

AFTER

Specifies that the trigger performs after the triggering event.

CURRENT VALIDTIME

Specifies that the trigger fires when the triggering event is current in the valid-time dimension.

The subject table must have valid time.

VALIDTIME

Specifies that the trigger fires when the triggering event is sequenced in the valid-time dimension.

The subject table must have valid time.

SEQUENCED VALIDTIME

NONSEQUENCED VALIDTIME

Specifies that the trigger fires when the triggering event is nonsequenced in the valid-time dimension.

The subject table must have valid time.

NONTEMPORAL

Specifies that the trigger fires when the triggering event is nontemporal in the transaction-time dimension. In this case, the triggering statement must specify the NONTEMPORAL prefix.

The triggered action statement can modify values in the transaction-time column.

The subject table must have transaction time. If the subject table has valid time, the qualifier in the valid-time dimension defaults to NONSEQUENCED VALIDTIME.

The NONTEMPORAL privilege is required to use the NONTEMPORAL option.

INSERT

Specifies that the triggering event for this trigger is one of the following:

• INSERT
• INSERT... SELECT
• Atomic Upsert
• MERGE

Triggers cannot be fired for rows that have been normalized. For more information on the NORMALIZE keyword, see CREATE TABLE/CREATE TABLE ... AS (Temporal Forms) and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

DELETE

Specifies that the triggering event for this trigger is a DELETE.

UPDATE

Specifies that the triggering statement for this trigger is one of the following:

• UPDATE
• Atomic Upsert
• MERGE

Any number of rows, including none, can be updated.

Triggers cannot be fired for rows that have been normalized. For more information on the NORMALIZE keyword, see CREATE TABLE/CREATE TABLE ... AS (Temporal Forms) and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

ON [database_name.]table_name

Specifies the subject table with which this trigger is associated.

If the subject table has valid time, and the triggering statement is not preceded by a valid-time qualifier, the default is CURRENT VALIDTIME. If the subject table has transaction time, and the triggering statement is not preceded by NONTEMPORAL, the default is CURRENT TRANSACTIONTIME.

ORDER integer

Specifies the order of trigger execution within a request when multiple triggers are defined on a subject table.

REFERENCING

Specifies a transition table that the WHEN condition and the triggered actions of a trigger can reference. The clause is optional and has no default.

FOR EACH ROW

Specifies keywords specifying that the trigger is to fire for each qualified row. That is, each row that evaluates to TRUE for any WHEN condition specified for the trigger.

FOR EACH STATEMENT

Specifies keywords specifying that the trigger is to fire once per processed SQL statement in the request whenever a WHEN condition for the trigger evaluates to TRUE.

WHEN (search_condition)

Specifies a search condition clause to refine the conditions that fire the trigger.

SQL_procedure_statement

Specifies one or more valid triggered action statements.

If the subject table is a nontemporal table and the trigger action references a temporal table, a current qualifier is applied to the trigger action statements.

If the subject table is a temporal table, all of the triggered action statements inherit the qualifier of the triggering statement. If an action requires a different qualifier, include the statement in a stored procedure and call the stored procedure in an action statement.

BEGIN ATOMIC

Specifies a keyword introducing multiple triggered action statements.

If you begin the triggered SQL statement clause with BEGIN ATOMIC, then you must also terminate it with the END keyword.