15.00 - CREATE TRIGGER/REPLACE TRIGGER (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

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

CREATE TRIGGER/REPLACE TRIGGER (Temporal Form)

Purpose  

CREATE TRIGGER creates a trigger on a subject table that is a temporal table.

REPLACE TRIGGER redefines an existing trigger or, if the specified trigger does not exist, creates a new trigger with the specified name.

Syntax  

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

Note: To ensure application portability to future ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.

 

Syntax Element …

Specifies …

database_name

an optional qualifier for trigger_name.

trigger_name

the name of the trigger to be created or replaced.

ENABLED

the keyword that enables a trigger to execute.

DISABLED

the keyword that disables a trigger from executing.

BEFORE

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

AFTER

that the trigger performs after the triggering event.

CURRENT VALIDTIME

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

The subject table must have valid time.

VALIDTIME

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

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

The subject table must have valid time.

NONTEMPORAL

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

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

  • INSERT
  • INSERT... SELECT
  • Atomic Upsert
  • MERGE
  • Note: Triggers cannot be fired for rows that have been normalized. For more information on the NORMALIZE keyword, see “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65 and SQL Data Definition Language.

    DELETE

    that the triggering event for this trigger is a DELETE.

    UPDATE

    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.

    Note: Triggers cannot be fired for rows that have been normalized. For more information on the NORMALIZE keyword, see “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65 and SQL Data Definition Language.

    ON [database_name.]table_name

    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

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

    REFERENCING

    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

    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

    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)

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

    SQL_procedure_statement

    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

    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.

    Recursive Triggers and Multiset Tables

    Temporal tables are multiset tables. If the subject table is referenced in the action, due to the multiset nature of the subject table, recursive triggers can hit the recursion limit and cause an error to be reported.

    Related Information

     

    For more information on...

    See...

    CREATE TRIGGER (regular form)

    SQL Data Definition Language

    NONTEMPORAL operations

  • “Nontemporal Operations” on page 25
  • “NONTEMPORAL Privilege” on page 181