REFERENCING Clause for Insert triggers

The REFERENCING clause for an Insert trigger can declare a correlation name for the inserted value in a column.


Element	Description	Restrictions	Syntax
correlation	Name that you declare here to qualify a new column value (as correlation.column) in a triggered action	Must be unique within this CREATE TRIGGER statement	Identifier

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 REFERENCING clause for INSERT triggers allows you to refer to data values produced by the trigger action.