Row and Statement Triggers | VantageCloud Lake - Row and Statement Triggers

Row and Statement Triggers | VantageCloud Lake - Row and Statement Triggers - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

Language

English (United States)

Last Update

2024-04-03

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

Triggers are of two mutually exclusive types: row and statement. You cannot combine row and statement operations within a single trigger definition.

Both row and statement triggers can call stored procedures (see CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)).

The following table summarizes the key differences between row and statement triggers.

Row Trigger	Statement Trigger
Fires once for each row changed by the triggering statement. The process of firing a trigger includes testing WHEN conditions for their truth value and not firing if the specified condition evaluates to FALSE. There is no limit on the number of times a row trigger fires.	Fires once. Statement triggers cannot access changed rows.
Does not fire if no row is modified by the triggering statement.	Fires even if no row is modified by the triggering statement.
Can be either a BEFORE or AFTER trigger.	Can only be an AFTER trigger.
Can access any OLD values, NEW values; or any OLD TABLE, OLD_TABLE, NEW TABLE, or NEW_TABLE transition tables that make sense by means of the REFERENCING clause. Performing an INSERT with an OLD [ROW] transition variable or an OLD TABLE transition table makes no sense, because there can be no pre-existing variable or table (see the next table for a complete list of valid and non-valid transition variables and tables). See the table beginning on the following page for details of which of these transitions are valid for a given triggered action statement.	Cannot use the REFERENCING options of OLD and NEW transition table names, only the OLD TABLE, OLD_TABLE, NEW TABLE, and NEW_TABLE transition table names. See the table beginning on the following page for details of which of these transitions are valid for a given triggered action statement.
Cannot specify OLD_NEW_TABLE transition tables. There is no analog of OLD_NEW_TABLE transition tables for row triggers.	Can specify OLD_NEW_TABLE transition tables.

If your application can use a row trigger or a statement trigger, you typically achieve better performance using the statement trigger, which fires only once, while the equivalent row trigger fires once for each row that the triggering statement updates.

The following table summarizes the valid combinations of trigger types, trigger activation times, update operations, and transition variables and tables:

Trigger Type	Activation Time	Update Operation	Valid Transitions
ROW	BEFORE	DELETE	OLD [ROW]
		INSERT	NEW [ROW]
		MERGE INSERT	NEW [ROW]
		MERGE UPDATE	OLD [ROW] NEW [ROW]
		UPDATE	OLD [ROW] NEW [ROW]
	AFTER	DELETE	OLD [ROW] OLD_TABLE OLD TABLE
		INSERT	NEW [ROW] NEW_TABLE NEW TABLE
		MERGE INSERT	NEW [ROW] NEW_TABLE NEW TABLE
		MERGE UPDATE	NEW [ROW] NEW_TABLE NEW TABLE OLD [ROW] OLD_TABLE OLD TABLE
		UPDATE	NEW [ROW] NEW_TABLE NEW TABLE OLD [ROW] OLD_TABLE OLD TABLE
STATEMENT	BEFORE	Not applicable. BEFORE STATEMENT triggers are not defined by the ANSI SQL:2011 standard or supported by Teradata.
	AFTER	DELETE	OLD_TABLE OLD TABLE
		INSERT	NEW_TABLE NEW TABLE
		MERGE INSERT	NEW_TABLE NEW TABLE
		MERGE UPDATE	NEW_TABLE NEW TABLE OLD_TABLE OLD TABLE
		UPDATE	NEW_TABLE NEW TABLE OLD_TABLE OLD TABLE OLD_NEW_TABLE