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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Mutating Table error - AFTER INSERT/UPDATE trigger

Re: Mutating Table error - AFTER INSERT/UPDATE trigger

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 24 Feb 2004 16:39:02 -0500
Message-ID: <cLOdnQ4dgucgWabdRVn-hA@comcast.com>


sorry about the empty post (musta had a mutating mouse button error!)

anyway, when you think about what you're trying to capture, you probably really wouldn't want to do it in a row-level trigger, since the result of your SELECT COUNT(*) will likely change as subsequent rows are affected by your trigger (since both INSERT and UPDATE statements can affect more than one row)

without knowing exactly what you're trying to accomplish from a business logic standpoint, it's a bit hard to recommend anything specific, other than the general guideline that you use a package-level PL/SQL collection, clear it in the BEFORE statement trigger, populate it in the row-level trigger, and process it (ie, create your log entries) in the AFTER statement trigger

if you could describe the business functionality you're trying to support, maybe i could provide a few ideas

;-{ mcs

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:FNGdnaj6HeZuIqbdRVn-gw_at_comcast.com...
|
| "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:39:02 CST

Original text of this message

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