Working with NEW and OLD pseudo-records
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.