17.05 - Syntax - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K
Teradata Vantage™ - 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 document. For additional syntax, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 , Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 , and Teradata Vantage™ - SQL Data Control Language, B035-1149.
{ 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 } [;]
To ensure application portability to ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.
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.