RERERENCING Clauses

The REFERENCING clause for any event declares a correlation name that can be used to qualify column values in the triggering table. These names enable FOR EACH ROW actions to reference new values in the result of trigger events.

They also enable FOR EACH ROW actions to reference old column values that existed in the triggering table prior to modification by trigger events.

Correlation names are not valid if the triggered action includes both the INSERT statement and the BEFORE WHEN or AFTER WHEN keywords. This restriction does not affect triggered actions that specify the FOR EACH ROW keywords without the BEFORE or AFTER keywords, or that include no INSERT statement.

REFERENCING Clause for Delete

REFERENCING Clause for Delete:
 
|--REFERENCING--OLD--+----+--correlation------------------------|
                     '-AS-'
 

The correlation is a qualifier for the column value in the triggering table before the triggering statement executed. The correlation is in scope in the FOR EACH ROW trigger action list. See Correlated Table Action.

To use a correlation name in a trigger action to refer to an old column value, prefix the column name with the correlation name and a period ( . ) symbol. For example, if the NEW correlation name is post, refer to the new value for the column fname as post.fname.

If the trigger event is a DELETE statement, using the new correlation name as a qualifier causes an error, because the column has no value after the row is deleted. For the rules that govern the use of correlation names, see Using Correlation Names in Triggered Actions.

You can use the REFERENCING clause for Delete only if you define a FOR EACH ROW trigger action.

In Extended Parallel Server, the OLD correlation value cannot be a BYTE or TEXT value. That is, it cannot refer to a BYTE or TEXT column.

REFERENCING Clause for Insert

REFERENCING Clause for Insert:
 
|--REFERENCING--NEW--+----+--correlation------------------------|
                     '-AS-'
 

The correlation is a name for the new column value after the triggering statement has executed. Its scope of reference is only the FOR EACH ROW trigger action list; see Correlated Table Action. To use the correlation name, precede the column name with the correlation name, followed by a period ( . ) symbol. Thus, if the NEW correlation name is post, refer to the new value for the column fname as post.fname.

If the trigger event is an INSERT statement, using the old correlation name as a qualifier causes an error, because no value exists before the row is inserted. For the rules that govern how to use correlation names, see Using Correlation Names in Triggered Actions. You can use the INSERT REFERENCING clause only if you define a FOR EACH ROW trigger action.

The following example illustrates use of the INSERT REFERENCING clause. This example inserts a row into backup_table1 for every row that is inserted into table1. The values that are inserted into col1 and col2 of backup_table1 are an exact copy of the values that were just inserted into table1.

CREATE TABLE table1 (col1 INT, col2 INT);
CREATE TABLE backup_table1 (col1 INT, col2 INT);
CREATE TRIGGER before_trig
   INSERT ON table1    REFERENCING NEW AS new
   FOR EACH ROW
   (
   INSERT INTO backup_table1 (col1, col2)
   VALUES (new.col1, new.col2)
   );

As the preceding example shows, the INSERT REFERENCING clause allows you to refer to data values produced by the trigger action.

REFERENCING Clause for Update

REFERENCING Clause for Update:
 
                .-------------------------------------.
                V    (1)                              |
|--REFERENCING----+--------OLD-+--+----+--correlation-+---------|
                  |  (1)       |  '-AS-'
                  '--------NEW-'
 

1. Use path no more than once

The OLD correlation is the name of the value of the column in the triggering table before execution of the triggering statement; the NEW correlation identifies the corresponding value after the triggering statement executes.

The scope of reference of the correlation names that you declare here is only within the FOR EACH ROW trigger action list. See Correlated Table Action.

To refer to an old or new column value, prefix the column name with the correlation name and a period ( . ) symbol. For example, if the new correlation name is post, you can refer to the new value in column fname as post.fname.

If the trigger event is an UPDATE statement, you can define both old and new correlation names to refer to column values before and after the triggering UPDATE statement. For rules that govern the use of correlation names, see Using Correlation Names in Triggered Actions.

You can use the UPDATE REFERENCING clause only if you define a FOR EACH ROW trigger action.

In Extended Parallel Server, the OLD correlation value cannot be a BYTE or TEXT value. That is, it cannot refer to a BYTE or TEXT column.

REFERENCING Clause for Select (IDS)

REFERENCING Clause for Select:
 
|--REFERENCING--OLD--+----+--correlation------------------------|
                     '-AS-'
 

This has the same syntax as the REFERENCING Clause for Delete. The scope of reference of the correlation name that you declare here is only within the FOR EACH ROW trigger action list. See Correlated Table Action.

You use the correlation name to refer to an old column value by preceding the column name with the correlation name and a period ( . ) symbol. For example, if the old correlation name is pre, you can refer to the old value for the column fname as pre.fname.

If the trigger event is a SELECT statement, using the new correlation name as a qualifier causes an error because the column does not have a new value after the column is selected. For the rules that govern the use of correlation names, see Using Correlation Names in Triggered Actions.

You can use the SELECT REFERENCING clause only if you define a FOR EACH ROW trigger action.