Whenever arow-level trigger fires, the PL/SQL runtime engine creates and populates two data structures that function much like records. They are the NEW and OLD pseudo-records ("pseudo" because they don't share all the properties of real PL/SQL records). OLD stores the original values of the record being processed by the trigger; NEW contains the new values. These records have the same structure as a record declared using %ROWTYPE on the table to which the trigger is attached.
Here are some rules to keep in mind when working with NEW and OLD:
With triggers on INSERT operations, the OLD structure does not contain any data; there is no "old" set of values.
With triggers on UPDATE operations, both the OLD and NEW structures are populated. OLD contains the values prior to the update; NEW contains the values the row will contain after the update is performed.
With triggers on DELETE operations, the NEW structure does not contain any data; the record is about to be erased.
The NEW and OLD pseudo-records also contain the ROWID pseudo-column; this value is populated in both OLD and NEW with the same value, in all circumstances. Go figure!
You cannot change the field values of the OLD structure; attempting to do so will raise the ORA-04085 error. You can modify the field values of the NEW structure.
You cannot pass a NEW or OLD structure as a "record parameter" to a procedure or function called within the trigger. You can pass only individual fields of the pseudo-record. See the gentrigrec.sp script for a program that will generate code transferring NEW and OLD values to records that can be passed as parameters.
When referencing the NEW and OLD structures within the anonymous block for the trigger, you must preface those keywords with a colon, as in:
IF:NEW.salary > 10000 THEN...You cannot perform record-level operations with the NEW and OLD structures. For example, the following statement will cause the trigger compilation to fail:
BEGIN:new:= NULL; END;You can also use the REFERENCING clause to change the names of the pseudo-records within the database trigger; this allows you to write code that is more self-documenting and application-specific. Here is one example:
CREATE OR REPLACE TRIGGER audit_update AFTER UPDATE ON frame REFERENCING OLD AS prior_to_cheat NEW AS after_cheat FOR EACH ROWBEGIN INSERT INTO frame_audit (bowler_id, game_id, frame_number, old_strike, new_strike, old_spare, new_spare, old_score, new_score, change_date, operation) VALUES (:after_cheat.bowler_id,:after_cheat.game_id,:after_cheat.frame_number,:prior_to_cheat.strike,:after_cheat.strike,:prior_to_cheat.spare,:after_cheat.spare,:prior_to_cheat.score,:after_cheat.score, SYSDATE, 'UPDATE');END;Run the full_old_and_new.sql script to take a look at the behavior of the OLD and NEW pseudo-records.
