Re: Using :new and :old dynamically

This looks like an excelent solution to my problem.

Thanks for helping out a front-end developer in over his head... Mike Bradicich
Bradicich Consulting
Mike_at_Bradicich.com

Chris Hamilton wrote:

> 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
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Christopher Hamilton
> Oracle DBA -- Wall Street Sports
> chris_at_wallstreetsports.com
> http://www.wallstreetsports.com/
Received on Mon May 10 1999 - 16:23:47 CDT