| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Using :new and :old dynamically
On Fri, 07 May 1999 15:52:28 -0400, Mike Bradicich
<Mike_at_Bradicich.Com> wrote:
>Any suggestions to this one are greatly appreciated.
>
>I want to right a generic procedure, or reusable trigger code, that
>references the values of :new and :old dynamically. In otherwords, if I
>write code in a trigger for table A, I want to be able to reference the
>values in :new and :old without having to code each column name.
>
>I can extract the names of the columns in table A by doing a simple
>query against the sys.col table. Then for each one of those columns I
>want to write a record to a table showing the :old value and :new
>value. But I can't write dynamic code that references the :new and :old
>columns at run time!?
>
>I want a complete audit, BUT THE NORMAL ORACLE AUDIT COMMANDS WON'T WORK
>because they do not track the old and new values.
>
>I could write a separate and specific peice of code for every table, and
>every column, but I want to avoid that. I want to have one generic
>procedure, or at most a generic set of code that I can use in each
>trigger.
>
>Any ideas on referencing :NEW and :OLD dynamically, or creating an audit
>that tracks the new and old values without having to write specific code
>in every trigger?
On my web page -- http://www.serve.com/cowpb/chamilton.html -- you can download an audit trail application I wrote that does almost exactly that.
In my version, you still have to have a separate set of triggers for each table, but I have a script that automatically generates the triggers by querying the data dictionary.
The application is also presented in Chapter 13 (or is it 12?) of the O'Reilly book "Oracle Security", and has been presented at IOUW a few years ago.
Chris
 |
 |