15.00 - Triggers - Teradata Database

Triggers

Triggers are active database objects associated with a subject table. A trigger essentially consists of a stored SQL statement or a block of SQL statements.

Triggers execute when an INSERT, UPDATE, DELETE, or MERGE modifies a specified column or columns in the subject table.

Typically, a stored trigger performs an UPDATE, INSERT, DELETE, MERGE, or other SQL operation on one or more tables, which may possibly include the subject table.

Triggers in Teradata Database conform to the ANSI/ISO SQL:2008 standard, and also provide some additional features.

Triggers have two types of granularity:

Row triggers fire once for each row of the subject table that is changed by the triggering event and that satisfies any qualifying condition included in the row trigger definition.

Statement triggers fire once upon the execution of the triggering statement.

You can create, alter, and drop triggers.

IF you want to …	THEN use …
define a trigger	CREATE TRIGGER.
enable a trigger disable a trigger change the creation timestamp for a trigger	ALTER TRIGGER. Disabling a trigger stops the trigger from functioning, but leaves the trigger definition in place as an object. This allows utility operations on a table that are not permitted on tables with enabled triggers. Enabling a trigger restores its active state.
remove a trigger from the system permanently	DROP TRIGGER.

For details on creating, dropping, and altering triggers, see SQL Data Definition Language.

Process Flow

Note that this is a logical flow, not a physical re-enactment of how Teradata Database processes a trigger.

1 The triggering event occurs on the subject table.

2 A determination is made as to whether triggers defined on the subject table are to become active upon a triggering event.

3 Qualified triggers are examined to determine the trigger action time, whether they are defined to fire before or after the triggering event.

4 When multiple triggers qualify, then they fire normally in the ANSI-specified order of creation timestamp.

To override the creation timestamp and specify a different execution order of triggers, you can use the ORDER clause, a Teradata extension.

Even if triggers are created without the ORDER clause, you can redefine the order of execution by changing the trigger creation timestamp using the ALTER TRIGGER statement.

5 The triggered SQL statements (triggered action) execute.

If the trigger definition uses a REFERENCING clause to specify that old, new, or both old and new data for the triggered action is to be collected under a correlation name (an alias), then that information is stored in transition tables or transition rows:

OLD [ROW] values under old_transition_variable_name, NEW [ROW] values under new_transition_variable_name, or both.

OLD TABLE set of rows under old_transition_table_name, NEW TABLE set of rows under new_transition_table_name, or both.

OLD_NEW_TABLE set of rows under old_new_table_name, with old values as old_transition_variable_name and new values as new_transition_variable_name.

6 The trigger passes control to the next trigger, if defined, in a cascaded sequence. The sequence can include recursive triggers.

Otherwise, control passes to the next statement in the application.

7 If any of the actions involved in the triggering event or the triggered actions abort, then all of the actions are aborted.

Restrictions

Most Teradata load utilities cannot access a table that has an active trigger.

An application that uses triggers can use ALTER TRIGGER to disable the trigger and enable the load. The application must be sure that loading a table with disabled triggers does not result in a mismatch in a user defined relationship with a table referenced in the triggered action.

Other restrictions on triggers include:

BEFORE statement triggers are not allowed.

BEFORE triggers cannot have data-changing statements as triggered action (triggered SQL statements).

BEFORE triggers cannot access OLD TABLE, NEW TABLE, or OLD_NEW_TABLE.

Triggers and hash indexes are mutually exclusive. You cannot define triggers on a table on which a hash index is already defined.

A positioned (updatable cursor) UPDATE or DELETE is not allowed to fire a trigger. An attempt to do so generates an error.

You cannot define triggers on an error logging table.

Archiving Triggers

Triggers are archived and restored as part of a database archive and restoration. Individual triggers can be archived or restored using the ARCHIVE or RESTORE statements of the ARC utility.

Related Topics

For detailed information on …	See …
guidelines for creating triggers conditions that cause triggers to fire trigger action that occurs when a trigger fires the trigger action time when to use row triggers and when to use statement triggers	CREATE TRIGGER in SQL Data Definition Language.
temporarily disabling triggers enabling triggers changing the creation timestamp of a trigger	ALTER TRIGGER in SQL Data Definition Language.
permanently removing triggers from the system	DROP TRIGGER in SQL Data Definition Language.