What is the difference between "referencing old" & "referencing new" in a SQL stored procedure?

Question

I'm not sure if this syntax is Informix-specific, but I was having trouble creating a trigger until I found some syntax that included one, or both, of these lines:

CREATE TRIGGER      accuplacer_trig
UPDATE OF           processed
ON                  accuplacer_rec
REFERENCING OLD AS  proc_pre_upd 
FOR EACH ROW        (EXECUTE PROCEDURE accuplacer_proc(proc_pre_upd.exam_num));

Sometimes it's written as REFERENCING OLD AS alias1, NEW AS alias2

What is the difference between REFERENCING OLD AS and REFERENCING NEW AS, and what do they do/why are they needed?

Solution 1

"OLD" is the data that was in the row before the update. "NEW" is the data that will be in the row after the update is complete. You need to access this information if you want your trigger to act based on it; usually, you do want this, although not always.

This page has a more lengthy discussion of using this aspect of triggers in the context of Informix: http://www.pacs.tju.edu/informix/answers/english/docs/dbdk/infoshelf/sqlt/15.fm3.html

Solution 2

It's the values before and after the trigger has occurred; as outlined below.

Many triggered-SQL-statements need to refer to data that is currently being changed by the database event that caused them to fire. The triggered-SQL-statement might need to refer to the new (post-change or "after") values.

...Changed data can be referred to in the triggered-SQL-statement using transition variables or transition tables. The referencing clause allows you to provide a correlation name or alias for these transition variables by specifying OLD/NEW AS correlation-Name .