CREATE TRIGGER and REPLACE TRIGGER Syntax Elements - Teradata VantageCloud Lake

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. Must be unique in the containing database.

ENABLED

Enables a trigger to run.

ENABLED is the default.

DISABLED

Disables a trigger (prevents its running).

A disabled trigger exists, but cannot run unless re-enabled (see ALTER TRIGGER).

BEFORE

Specifies that the trigger performs before the triggering event, or triggering statement, runs.

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

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

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:

• Object File System table
• Global temporary table  
• 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.

Triggers with the same ORDER value, trigger action time, and trigger even 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 old_transition_variable_name or new_transition_variable_name, except 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.

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:

• Must begin with the SEMICOLON character that terminates the preceding line.
• Must specify the next triggered action statement in the list.

BEGIN ATOMIC

A keyword introducing multiple triggered action statements.

A triggered SQL statement clause that begins with BEGIN ATOMIC must end 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.