General Usage Guidelines for CREATE TRIGGER and REPLACE TRIGGER

Teradata Extensions to ANSI Triggers

Vantage triggers provide the following Teradata extensions to the ANSI SQL:2011 specification for CREATE TRIGGER.

ORDER clause
ENABLED/DISABLED option
OLD_NEW_TABLE transition table

Triggers and Query Bands

Neither the SESSION nor the TRANSACTION form of the SET QUERY_BAND statement can be specified as a triggered action statement in a trigger definition.

Database Objects that Cannot Be Referenced in a Trigger Definition

You cannot reference any of the following database objects in a trigger definition:

Global temporary tables
Recursive views

Trace tables
Views
Volatile tables

SET as a Triggered Action Statement

A SET clause assigns a value to a column, variable, or parameter.

You can use the SET clause in a trigger definition as a triggered SQL statement if the triggering action retains the row in the target table after its action is completed. For example, you can specify a SET clause in UPDATE and INSERT BEFORE row triggers, but not in a DELETE BEFORE row trigger because the DELETE statement removes a row set from the target table.

The following rules apply to the use of the SET clause:

SET can modify values of a new transition table row, called NEW ROW, but cannot modify the OLD ROW.
SET can use values from both the NEW ROW and OLD ROW.
Each SET clause can assign a value to only one column.
The scalar expression specified with a SET expression can contain any combination of the following elements:
- System variables.
- Functions with arguments that are scalar subexpressions.
- Scalar, but not aggregate, user-defined functions.
- Expressions referencing NEW or OLD row columns.
- Constants.
If a column name in the SET clause is not qualified explicitly, then the system qualifies that column with the correlation name for NEW row.
If there is a CHECK column or referential integrity constraint on the column to which the SET assignment clause is applied, then that constraint is enforced only on the final value of the column after all the SET clauses of the BEFORE row trigger are applied.
You can specify multiple SET clauses that update the same column.
When the same column is updated more than once in multiple SET clauses, the final value assigned to the column is the value assigned by the last SET clause in the sequence, and the earlier updates are lost.

Cascading and Recursion of Triggers

Cascading is not itself an element of triggers, but row triggers can create a cascade when a statement fires a trigger, which in turn fires another trigger, and so on. Thus the outcome of one triggering event can itself become another trigger.

The following rules apply to trigger cascading and recursion:

BEFORE trigger have no data-changing statements and therefore cannot cascade.
Trigger recursion, including self-referencing triggers, is valid. That is, you can make backward references to triggering statements in a cascade of triggers.
Cascaded row triggers that refer to a transition table are not valid.
To prevent an infinite recursive loop, the system imposes a limit of 16 cascaded or recursive triggers. This limit is enforced at run time, and the system rolls back requests with cascading of triggers that exceed this limit.

Triggers and Identity Columns

The following rules and restrictions apply to triggers and INSERT operations into tables with identity columns.

You cannot create a trigger definition if its triggering statement is an INSERT on a subject table that has an identity column and its triggered action statement or WHEN condition references that column.
If a triggered statement references rows in the table involved in the triggering statement, and the triggered statement is an INSERT operation into a table with a GENERATED ALWAYS identity column, Vantage substitutes a dummy value for the USING value for the referenced row and generates the actual identity column number in the Dispatcher before sending it to its destination AMP.

Restrictions and Limitations for NoPI Tables

You cannot create an UPDATE (Upsert Form) or MERGE trigger on a NoPI table.

Restrictions and Limitations for Normalized Table Columns

The following restrictions apply to using triggers with the columns of normalized tables.

You cannot specify a normalize Period column for the NEW ROW and NEW TABLE options.
You can specify a normalize Period column for the OLD ROW and OLD TABLE options.
Vantage does not fire a trigger on normalized rows for UPDATE or INSERT operations.

CREATE TRIGGER/REPLACE TRIGGER Restrictions and Limitations for Load Utilities

You cannot use FastLoad, MultiLoad, or Teradata Parallel Transporter to load data into base tables that have enabled triggers. Otherwise, the system returns an error message and the loading process aborts.

You can load data into the base tables if you first disable all triggers on those tables using the ALTER TRIGGER statement and then run the appropriate data loading utility.

You can then re-enable the disabled triggers after the data loading operation completes.

Load utilities like TPump that perform standard SQL inserts and updates can update tables that have enabled triggers.

Dropping or Deleting Database Objects that Contain Triggers

Vantage imposes restrictions on DROP and DELETE operations on the following database objects to make sure that triggers are not orphaned, as indicated by the following table:

Statement	State	Result
DROP TABLE	Triggers are defined on the specified table.	An error message. You must first drop all the triggers before the table can be dropped.
DELETE DATABASE DELETE USER	At least one table in the named database or user is the subject table of triggers created in a different database.	An error message. You must first drop all such triggers before the database or user can be deleted.

Triggers, Partitioned Primary Indexes, and the ALTER TABLE Statement

The following table shows which triggers must be disabled before you can perform an ALTER TABLE request that modifies or revalidates the primary index for a table.

ALTER TABLE Request Modifies or Revalidates the Primary Index with this Option	Disable These Triggers
WITH DELETE	All DELETE triggers on table_name.
WITH INSERT	All DELETE triggers on table_name. All INSERT triggers on save_table.

You can re-enable the triggers after the ALTER TABLE request completes.

Rules for Using Scalar Subqueries in Triggers

The following rules apply to specifying scalar subqueries in triggers:

You can specify an ABORT or ROLLBACK statement with a scalar subquery in the body of a trigger. However, Vantage processes any uncorrelated scalar subquery you specify in the WHERE clause of an ABORT statement in a row trigger as a single-column single-row spool instead of as a parameterized value.
You can specify a DELETE statement with a scalar subquery in the body of a trigger. However, Vantage processes any uncorrelated scalar subquery you specify in the WHERE clause of a DELETE statement in a row trigger as a single-column single-row spool instead of as a parameterized value.
You can specify an INSERT statement with scalar subqueries in the body of a trigger.
You cannot specify a uncorrelated scalar subquery as a value in the multivalue of a simple INSERT in the body of a row trigger.
Vantage processes any uncorrelated scalar subquery specified in the SELECT component of an INSERT … SELECT in a row trigger as a single-column single-row spool instead of as a parameterized value.
You can specify an UPDATE statement with scalar subqueries in the body of a trigger.
However, Vantage processes any uncorrelated scalar subqueries specified in the WHERE or SET clauses of an UPDATE statement in a row trigger as a single-column single-row spool instead of as a parameterized value.

General Usage Guidelines for CREATE TRIGGER and REPLACE TRIGGER - Teradata VantageCloud Lake

Lake - Working with SQL