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 were 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.