CREATE TRIGGER and REPLACE TRIGGER Syntax Elements - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
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 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
  • View
  • Volatile table   
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 run 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 ] [AS] old_transition_variable_name
Specifies a correlation name for the current row (transition row) before modification.
Valid only for ROW triggers on DELETE, MERGE UPDATE, and UPDATE events.
You can specify a normalize Period column for OLD [ ROW ].
NEW [ ROW ] [AS] new_transition_variable_name
Specifies a correlation name for the current row (transition row) after modification.
Valid only for ROW triggers on INSERT, MERGE INSERT, MERGE UPDATE, and UPDATE events.
You cannot specify a normalize Period column for NEW [ ROW ].
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 a Boolean condition that represents a valid search condition based on comparisons of items within the scope of the trigger definition. The trigger fires only if the value of search_condition is TRUE.
You must qualify columns in search_condition with either old_transition_variable_name or new_transition_variable_name unless they are in a subquery. For example:
CREATE TRIGGER RaiseTrig
  AFTER UPDATE OF salary ON employee
  REFERENCING OLD AS OldRow NEW AS NewRow
  FOR EACH ROW
    WHEN ((NewRow.salary - OldRow.salary) / OldRow.salary >.10)
    INSERT INTO salary_log
    VALUES ('USER', NewRow.name, OldRow.salary, NewRow.salary);
search_condition can contain aggregates only in a subquery. When you reference an OLD TABLE or NEW TABLE transition table from a search_condition, you must express the predicate 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.
search_condition can include UDT comparisons only if 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.
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
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.
In BTEQ, if you do not enclose the list of SQL procedure statements in parentheses, the line immediately following each statement must follow these rules:
  • It must begin with the SEMICOLON character that terminates the preceding line.
  • It must specify the next triggered action statement in the list.
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.