CREATE TRIGGER and REPLACE TRIGGER Syntax Elements - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
database_name_1
An optional qualifier if the trigger is being created in a database other than the default for the current user.
trigger_name
Name of the trigger being created or replaced.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
A trigger_name must be unique within the database in which it is created.
ENABLED
Keyword that enables a trigger to execute.
ENABLED is the default.
DISABLED
Keyword that disables a trigger from executing.
The definition of a disabled trigger remains is not dropped, but it must be enabled before it can execute (see ALTER TRIGGER).
BEFORE
Specifies that the trigger performs before the triggering event, or triggering statement, is executed.
  • A BEFORE row trigger cannot have data-changing statements as triggered action SQL statements.
  • A BEFORE statement trigger is not valid under any circumstances.
The database returns an error message in both cases.
AFTER
The trigger performs after the triggering event.
temporal_option
See Teradata Vantage™ - Temporal Table Support , B035-1182 for documentation of these options.

triggering_event

A triggering event, or triggering statement, can be an INSERT, DELETE, UPDATE, or MERGE request. See Teradata Vantage™ - Temporal Table Support , B035-1182 for documentation of the rules that apply to triggering statements for temporal tables.

INSERT
The triggering event for this trigger is one of the following:
  • INSERT
  • INSERT... SELECT
  • Atomic Upsert
  • MERGE
DELETE
The triggering event for this trigger is a DELETE.
UPDATE
The triggering statement for this trigger is one of the following:
  • UPDATE
  • Atomic Upsert
  • MERGE
Any number of rows, including none, can be updated.
column_name
Name of a column in a set of column names.
The trigger fires when any column in the list is updated.
You can optionally enclose the list in parentheses.
If you do not specify a column name list, the default is all columns.
The list cannot specify duplicate column names.
The column_name list does not apply to INSERT or DELETE triggering events.
database_name_2
Optional qualifier for the subject table.
If the subject table is in a database other than the default for the current user, this specification is mandatory.
table_name
Name of the subject table with which this trigger is associated.
table_name must be the name of an existing base table.
The object referenced by table_name cannot be any of the following:
  • global temporary table  
  • hash index
  • join index
  • recursive view
  • queue table   
  • trace table
  • volatile table   
  • view
ORDER

ORDER values determine the execution sequence when two or more triggers have the identical trigger action time and trigger event.

If triggers have the same ORDER value, trigger action time, and trigger event, then they execute in order of their creation timestamp.
integer
Value assigned to ORDER.
The value must be any positive small integer not greater than 32,767.
If the ORDER clause is not specified, the system assigns a default value of 32,767 to the trigger.

reference

The REFERENCING clause allows the WHEN condition and the triggered actions of a trigger to reference the set of rows in one or more transition tables. The clause is optional and has no default.

The semantics of this clause are identical for temporal and nontemporal tables.

OLD [ ROW ]
Introduction to a correlation name for the current row before it is modified. The current row is called a transition row. The ROW keyword is optional.
You can specify OLD or OLD ROW only for ROW triggers.
You can specify a normalize Period column for OLD or OLD ROW.
OLD and OLD ROW are valid only for DELETE, MERGE UPDATE, and UPDATE triggering events.
old_transition_variable_name
Row alias name specified with the OLD or OLD ROW option used for referencing old values in the current row of the transition table.
The AS keyword preceding old_transition_variable_name is optional.
NEW [ ROW ]
Introduction to a correlation name for the current row after it is modified. The current row is called a transition row. The ROW keyword is optional.
You can specify NEW or NEW ROW only for ROW triggers.
You cannot specify a normalize Period column for NEW or NEW ROW.
NEW and NEW ROW are valid only for INSERT, MERGE INSERT, MERGE UPDATE, and UPDATE triggering events.
AS new_transition_variable_name
Row alias name specified with the NEW or NEW ROW option used for referencing new values in the current row of the transition table.
OLD_TABLE
OLD TABLE
Equivalent introductions to a table correlation name for the old values transition table.
OLD TABLE is the form specified by the ANSI SQL-2011 standard.
OLD_TABLE is a Teradata extension retained for backward compatibility.
You can specify OLD_TABLE or OLD TABLE references in both statement triggers and row triggers.
You can specify a normalize Period column for OLD TABLE or OLD_TABLE.
OLD_TABLE and OLD TABLE are valid only for DELETE, MERGE UPDATE, and UPDATE triggering events.
old_transition_table_name
Table alias name specified with the OLD TABLE or OLD_TABLE options for referencing the transition table of old values.
NEW_TABLE
NEW TABLE
Equivalent introductions to a table correlation name for the new values transition table.
NEW TABLE is the form specified by the ANSI SQL:2011 standard.
NEW_TABLE is a Teradata extension retained for backward compatibility.
You can specify NEW_TABLE or NEW TABLE references in both statement triggers and row triggers.
You cannot specify a normalize Period column for NEW ROW TABLE or NEW_TABLE.
NEW_TABLE and NEW TABLE are valid only for INSERT, MERGE INSERT, MERGE UPDATE, and UPDATE triggering events.
new_transition_table_name
Table alias name specified with the NEW TABLE or NEW_TABLE options for referencing the transition table of new values.
OLD_NEW_TABLE
Introduction to a table correlation name for the old and new values transition table.
You can only specify an OLD_NEW_TABLE reference for an AFTER UPDATE trigger.
This clause provides two advantages for statement triggers over the NEW TABLE and OLD TABLE syntax:
  • Enables a statement trigger to store all the old and new values in a single row.
  • Eliminates the need for a self-join between the transition table and itself.
  • Enables the capability to compare the old value with its corresponding new value in the affected row rather than comparing the old value with the new values in all affected new rows.
old_new_table_name
Table alias name specified with the OLD_NEW_TABLE option for referencing the transition table of old and new values in the same row.
old_value
An alias you can use to reference specific old value columns of the row in the old new table transition table.
new_value
An alias you can use to reference specific new value columns of the row in the old new table transition table.
FOR EACH ROW
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 is the default.
FOR EACH STATEMENT
The trigger is to fire once per processed SQL statement in the request whenever a WHEN condition for the trigger evaluates to TRUE.
FOR EACH STATEMENT is the default.
WHEN (search_condition)
Logic that further refines the conditions for firing the trigger.
search_condition is any valid search condition based on comparisons of items within the scope of the trigger definition. This includes UDT comparisons as long as ordering has been defined for the UDTs. See CREATE ORDERING and REPLACE ORDERING.
If the trigger is a row trigger, then search_condition is based on row correlation names for the current row.
If search_condition specifies a subquery, then it can contain aggregates. Otherwise, aggregates are not valid as part of a WHEN clause search condition specification.
This is important because any time you reference an OLD TABLE or NEW TABLE transition table from a WHEN clause search condition, the predicate must be expressed as a subquery. Because a WHEN condition must provide a single result, the typical use of OLD TABLE and NEW TABLE column references is with aggregates.
A Boolean condition. Any expression that does not evaluate to TRUE or FALSE is not valid either as a search condition or as a cursor name.
search_condition is evaluated once for each:
  • execution of the triggering statement for statement triggers
  • row of the transition table of changed rows for row triggers
Whether a triggered action is taken or not depends on the evaluation of the search condition result, as described by the following table.
search_condition Evaluation Statement or Row
TRUE Qualifies to fire the triggered action.
FALSE Does not qualify to fire the triggered action.
UNKNOWN Does not qualify to fire the triggered action.
trigger_action
The ANSI SQL:2011 specification requires multiple statements to be enclosed within the BEGIN ATOMIC and END keywords. Vantage complies with this, and also allows you to specify multiple SQL procedure statements without the BEGIN ATOMIC and END keywords.
Each triggered action statement in an SQL procedure statement list must be terminated by a SEMICOLON (;) character.
You can specify the list of SQL procedure statements either enclosed or not enclosed by parentheses.
If you are typing the SQL text using BTEQ and you specify the list of SQL procedure statements without enclosing it in parentheses, then the SEMICOLON character terminating each statement must be typed at the beginning of the next line following the statement line. The next triggered action statement in the list must be continued in the same line as the SEMICOLON character.
UDT expressions are valid in triggered SQL statements.
See Teradata Vantage™ - Temporal Table Support , B035-1182 for details and examples of using triggered SQL statements with temporal tables.
BEGIN ATOMIC
A keyword introducing multiple triggered action statements.
If you begin the triggered SQL statement clause with BEGIN ATOMIC, you must terminate it with the END keyword.
SQL_procedure_statement
One or more valid triggered action statements.
END
A keyword terminating the statement block introduced by the BEGIN ATOMIC keywords.
If you use BEGIN ATOMIC, you must specify END to terminate the triggered SQL statements clause.