"M Mueller" <melissa_mueller_at_yahoo.com> wrote in message
news:ac8fbcca.0402241218.71c3681c_at_posting.google.com...
| 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 - 15:18:36 CST
