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 (External Form) and CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)).
The following table summarizes the key differences between row and statement triggers.
A row trigger … | A 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 only 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. For example, it makes no sense to perform an INSERT with an OLD [ROW] transition variable or an OLD TABLE transition table 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 be written using either a row trigger or a statement trigger, you can almost always achieve better performance using the statement trigger because it 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 |
|
||
UPDATE |
|
||
AFTER | DELETE |
|
|
INSERT |
|
||
MERGE INSERT |
|
||
MERGE UPDATE |
|
||
UPDATE |
|
||
STATEMENT | BEFORE | Not applicable. BEFORE STATEMENT triggers are not defined by the ANSI SQL:2011 standard, nor are they supported by Teradata. |
|
AFTER | DELETE |
|
|
INSERT |
|
||
MERGE INSERT |
|
||
MERGE UPDATE |
|
||
UPDATE |
|