🚀 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..."