dynamic trigger variables?

Does anyone know of a way to reference the :old and :new trigger variables in a dynamic sort of way? Hopefully the example will do a better job of explaining my predicament.

I am writing a trigger that will simply create an audit trail of updates into a history table. The history table simply has column_changed, oldvalue, newvalue and changed_date.

I need to record everytime a column is changed. If 5 columns in the same row change (via one update statement), then the history table should get 5 rows added.

If Oracle had a "for each column" clause of an update trigger, I'd be set. But since I don't know of anyway to get oracle to tell me which columns have been updated, it's messy.

Instead of writing a big "if" statement to check each column that is being updated, I am trying to loop through all the columns in the table by querying the dictionary view all_tab_columns.

The relevant snippets of the code look like this:

declare cursor c1(tabname varchar2) is select column_name from all_tab_columns where table_name=tabname; r1 c1%rowtype;

begin
if updating then

 open c1('DOMAIN');
  loop

  fetch c1 into r1;
  exit when c1%notfound or c1%notfound is null;

  if updating (r1.column_name) then

    insert into hist (domain_id, domain_name, action_date,     action_taken, field, oldvalue, newvalue)       values
    :old.domain_id, :new.name,sysdate, 'updated',     r1.column_name,:old.r1.column_name, :new.r1.column_name);

   end if;

  end loop;
 close c1;

end if;
end;

The trouble is with the
syntax: :old.r1.column_name, :new.r1.column_name. Everything else works. I tried setting them to variables first but that didn't work either. It seems like the only thing that can come after :old or :new is a column name.

I know it's wrong. I can't think of anyway to dynamically get these values in a looping structure like this. Is it possible?

Any ideas?

thanks,
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 30 2000 - 20:34:28 CST