Re: Mutating tables

Simply placing the select statement in a statement level trigger instead in a row level trigger gives the solution. In addition, you need to store the old en new values in something like a packaged pl/sql table, which you can acces in the statement level trigger. Filling the pl/sql table occurs in the row level trigger. Besides, the query is a little bit useless when inserting, since in that case old values simply don't exists, i.e. equals null (numofdocs whill alwasy equal 0 when inserting). "M Mueller" <melissa_mueller_at_yahoo.com> schrieb im Newsbeitrag news:ac8fbcca.0402240557.55d69a39_at_posting.google.com...
> Hello -
>
> I'm using Oracle 8i and am running into the infamous mutating table
> error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
> examples for getting around mutating table errors if you either need
> access to the :old or the :new values within a trigger. But what if
> you need access to both? Or is this even possible (I'm fairly new to
> triggers).
>
> I have to execute a select count(*) from trigger_table where
> value1=:old.value1 and value2=:old.value2. Based on this result, log
> files must be written utilizing the :new values. A simplified example
> of my trigger is below:
>
> CREATE OR REPLACE TRIGGER mut_trigger
> AFTER INSERT OR UPDATE OF value1, value2
> ON table_a
> REFERENCING OLD AS OLD NEW AS NEW
> FOR EACH ROW
> DECLARE
> numofdocs number;
> BEGIN
> select count(*) into numofdocs from table_a where value1=:old.value1
> and value2=:old.value2;
> if (numofdocs > 0) then
> insert into log_table1 values ('Log data', :new.value1,
> :new.value2);

 insert into log_table2 values ('Log data', :new.value1,
> :new.value2);
> end if;
> END;
>
> Is there any way to get what I need using one trigger?
>
> Thanks so much,
> Melissa
Received on Fri Mar 05 2004 - 21:32:14 CET