Using :new and :old dynamically 
Author Message
 Using :new and :old dynamically

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?

Your ideas are very much appreciated.
Mike Bradicich
Bradicich Consulting



Tue, 23 Oct 2001 03:00:00 GMT
 Using :new and :old dynamically

On Fri, 07 May 1999 15:52:28 -0400, Mike Bradicich

Quote:

>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

http://www.wallstreetsports.com/



Wed, 24 Oct 2001 03:00:00 GMT
 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

Quote:

> On Fri, 07 May 1999 15:52:28 -0400, Mike Bradicich

> >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

> http://www.wallstreetsports.com/



Fri, 26 Oct 2001 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Master, msdb and model (old version and new version) i need old version

2. Transfer one older database backup from older SQL 7 server to a new one

3. Using :new and :old as parameters to Stored Proc

4. re-using old PC's as telnet terminals using Linux

5. create new tables dynamically while importing from excel

6. Dynamically add new columns

7. Dynamically creating a new database from code?

8. How do I create new databases dynamically???

9. Dynamically allocated new extension shared memory

10. Why IDS dynamically allocate new shared memory segment

11. dynamically allocated new shared memory segment - What is thi

12. dynamically allocated new shared memory segment - What is


 
Powered by phpBB® Forum Software