Audit Trigger

[Quoted] [Quoted] 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 [Quoted] 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,

• 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. Received on Wed Aug 22 2001 - 19:51:48 CEST