CREATE TRIGGER/REPLACE TRIGGER Syntax Elements (Temporal Form) - Advanced SQL Engine - Teradata Database

Temporal Table Support
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
bud1592002688266.ditamap
dita:ditavalPath
bud1592002688266.ditaval
dita:id
B035-1182
lifecycle
previous
Product Category
Teradata Vantage™
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.