🚀 go-pugleaf

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.