- 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.