Using Correlation Names in Triggered Actions

These rules apply when you use correlation names in triggered actions:

• You can use the correlation names for the old and new column values in SQL statements of the FOR EACH ROW triggered-action list and in the WHEN condition.
• The old and new correlation names refer to all rows affected by the triggering statement.
• You cannot use the correlation name to qualify a column name in the GROUP BY, the SET, or the COUNT DISTINCT clause.
• The scope of reference of the correlation names is the entire trigger definition. This scope is statically determined, meaning that it is limited to the trigger definition; it does not encompass cascading triggers or columns that are qualified by a table name in a UDR that is a triggered action.

When to Use Correlation Names

In SQL statements of the FOR EACH ROW list, you must qualify all references to columns in the triggering table with either the old or new correlation name, unless the statement is valid independent of the triggered action.

In other words, if a column name inside a FOR EACH ROW triggered action list is not qualified by a correlation name, even if it is qualified by the triggering table name, it is interpreted as if the statement is independent of the triggered action. No special effort is made to search the definition of the triggering table for the non-qualified column name.

For example, assume that the following DELETE statement is a triggered action inside the FOR EACH ROW section of a trigger:

DELETE FROM tab1 WHERE col_c = col_c2

For the statement to be valid, both col_c and col_c2 must be columns from tab1. If col_c2 is intended to be a correlation reference to a column in the triggering table, it must be qualified by either the old or the new correlation name. If col_c2 is not a column in tab1 and is not qualified by either the old or new correlation name, you get an error.

In a statement that is valid independent of the triggered action, a column name with no correlation qualifier refers to the current value in the database.

In the triggered action for trigger t1 in the next example, mgr in the WHERE clause of the correlated subquery is an unqualified column in the triggering table. In this case, mgr refers to the current column value in empsal because the INSERT statement is valid independent of the triggered action.

CREATE DATABASE db1;
CREATE TABLE empsal (empno INT, salary INT, mgr INT);
CREATE TABLE mgr (eno INT, bonus INT);
CREATE TABLE biggap (empno INT, salary INT, mgr INT);

CREATE TRIGGER t1 UPDATE OF salary ON empsal
AFTER (INSERT INTO biggap SELECT * FROM empsal WHERE salary <
   (SELECT bonus FROM mgr WHERE eno = mgr));

In a triggered action, an unqualified column name from the triggering table refers to the current column value, but only when the triggered statement is valid independent of the triggered action.

Qualified Versus Unqualified Value

The following table summarizes what value is retrieved when the column name is qualified by the old or by the new correlation name after various trigger events.

Refer to the following key when you read the previous table.

Term

Meaning

Original value

Value before the triggering statement

Current value

Value after the triggering statement

(N)

Cannot be changed by triggered action

(U)

Can be updated by triggered actions; updated value might be different from the original value because of preceding triggered actions.

Outside a FOR EACH ROW triggered-action list, you cannot qualify a column from the triggering table with either the old correlation name or the new correlation name; it always refers to the current value in the database.

In Dynamic Server, statements in the trigger action list use whatever collating order was in effect when the trigger was created, even if a different collation is in effect when the trigger action is executed. See SET COLLATION for details of how to specify a collating order different from what DB_LOCALE specifies.