Re::new & :old as records ?

"Alexandr I. Alesinsky" <al_at_investor.kharkov.ua> wrote:

>When I wrote a row-level triggers in Oracle I frequently need to pass a whole
>set of record fields values to a procedure. But I can't. :new and :old is only
>referencing, they aren't treated as real records by Oracle. If I wrote
>somewhere in the trigger
 

>my_procedure(:new)

This gives me opportunity to post an idea that I have been batting around lately.

Allow wildcard (*) with :new and :old designation.

Possible usages:

1. my_procedure(:new.*) or my_procedure(:new.*, :old.*) Allows passing of entire data record as it existed before and/or after update or delete.
2. insert into my_table_history values (:old.*,sysdate,user) Allows writing entire record to audit table and avoids the mutating table error for triggers that are 'for each row' and trying to select record from table.
3. where :new.* != :old.* if :new.* = :old.* would have to consider null to equal null and would be a way to see if any data really did change (in other words, user did not change '1' to '1') before processing rest of trigger. Do not want to track in audit trail.

So what do you think?

-- 
McKendry's Uncertainty Principle:
At any given moment, one cannot be certain that any opinion is mine--much 
less SSC's or any client's.

Keith McKendry                     |  73071.3233_at_compuserve.com
SSC Systems Management Consulting  |