Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Mutating tables

Mutating tables

From: M Mueller <melissa_mueller_at_yahoo.com>
Date: 24 Feb 2004 05:57:17 -0800
Message-ID: <ac8fbcca.0402240557.55d69a39@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 Tue Feb 24 2004 - 07:57:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US