Re: Audit Trigger

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