Re: Mutating tables

_at_#$$#%^%%&^
"M Mueller" <melissa_mueller_at_yahoo.com> wrote in message 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 Wed Jun 09 2004 - 16:31:51 CEST