Re: dynamic trigger variables?

I don't think you can do it - but you can write a script that will at least dynamically create the trigger.

John Alexander
St. Petersburg, FL
www.SummitSoftwareDesign.com
<gdas_at_my-deja.com> wrote in message news:8tlb3k$nn8$1_at_nnrp1.deja.com...
> 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 - 21:04:10 CST