🚀 go-pugleaf

Adriaan Nortje wrote:
> 
> Hi
> 
> I'm trying to create a generic audit trigger (after update) which will
> select from user_tab_colums all the columns of the table and then go
> evaluate the :old. and :new. values for each colun to see whether there was
> an updte done on the column and insert it into an audit table.
> 
> what I was thinking of doing, is something like this.
> 
> create or replace trigger audit_trigger
> after update or insert ot delete on employees for each row
> 
> declare
>     cursor aud_cur is select column_name , column_type from user_tab_columns
> where table_name = 'employees';
> 
> begin
>     if updating then
>         for aud_row in aud_cur loop
>             ------here is my problem -----
>             if :old.aud_row.column_name <> :new.aud_row.column_name then
>                 insert into audit_table values (:old.aud_row.column_name,
> :new.aud_row.column_name)
> 
> --- bla, bla, bla
> 
> as you can see this will clearly not work. What needs to be done is that I
> need the value of the column_name and somehow get the :old. and :new. value
> for that column_name's value (if that makes any sense at all). In a normal
> procedure I would have been able to select two values like these using
> NATIVE DYNAMIC SQL, but as this is a trigger I can not and any attempt to
> query the table again will result in a mutation error and then I still only
> have one value (if you can get around the mutation)
> 
> Oracle's auditing features don'r allow for auditing of column specific
> auditing and can only tell me that the table has been access for an update.
> Also, passing the column_name to a external procedure (like Pro C++) would
> result in errors and still I have no new and old values.
> 
> I've been trying to find a function in PL similar to Developer's namein
> function which will do exactly what is required, but I haven't had any luck
> yet.
> 
> So, PLEASE help me.
> 
> GREAT.

You need another layer of abstraction, ie, write a little script that
will generate all the trigger code (using user_tab_columns to do it) for
each table that you want to monitor.

hth
connor  
-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."

Even if you could get this to work, I'd advise against having a trigger do
looping based on the data dictionary from a performance perspective.
Granted, you want the trigger to be flexible, but you need to weigh
flexibility against scalability, especially if the application (or the
instance it is using) needs to handle large volumes.

I'd suggest creating a SQL script, or program if you prefer, to generate the
trigger from the dictionary.  Any time you modify the table structure -- or
add a table, the trigger could be regenerated to reflect the columns in the
table.

It may not be as elegant, but you'll spend less time trying to figure out
why applications start slowing down once the database gets busy or the
dictionary gets large/fragmented because a lot of DB objects have been
created.

Adriaan Nortje wrote in message <3b83f0b8$0$233@hades.is.co.za>...
>Hi
>
>I'm trying to create a generic audit trigger (after update) which will
>select from user_tab_colums all the columns of the table and then go
>evaluate the :old. and :new. values for each colun to see whether there was
>an updte done on the column and insert it into an audit table.
>
>what I was thinking of doing, is something like this.
>
>create or replace trigger audit_trigger
>after update or insert ot delete on employees for each row
>
>declare
>    cursor aud_cur is select column_name , column_type from
user_tab_columns
>where table_name = 'employees';
>
>begin
>    if updating then
>        for aud_row in aud_cur loop
>            ------here is my problem -----
>            if :old.aud_row.column_name <> :new.aud_row.column_name then
>                insert into audit_table values (:old.aud_row.column_name,
>:new.aud_row.column_name)
>
>
>--- bla, bla, bla
>
>as you can see this will clearly not work. What needs to be done is that I
>need the value of the column_name and somehow get the :old. and :new. value
>for that column_name's value (if that makes any sense at all). In a normal
>procedure I would have been able to select two values like these using
>NATIVE DYNAMIC SQL, but as this is a trigger I can not and any attempt to
>query the table again will result in a mutation error and then I still only
>have one value (if you can get around the mutation)
>
>Oracle's auditing features don'r allow for auditing of column specific
>auditing and can only tell me that the table has been access for an update.
>Also, passing the column_name to a external procedure (like Pro C++) would
>result in errors and still I have no new and old values.
>
>
>I've been trying to find a function in PL similar to Developer's namein
>function which will do exactly what is required, but I haven't had any luck
>yet.
>
>So, PLEASE help me.
>
>GREAT.
>
>
>
>
>