Mutating Table error - AFTER INSERT/UPDATE trigger

Hello -

I believe I posted this in the wrong ng before - sorry for the double post (if you noticed the other message).

I'm using Oracle 8.1.7 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 Tue Feb 24 2004 - 14:18:32 CST