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: Need workaround for Oracle Mutating table

Re: Need workaround for Oracle Mutating table

From: Oradba Linux <oradba_linux_at_verizon.net>
Date: Sun, 04 Feb 2007 03:49:18 GMT
Message-ID: <2Jcxh.18188$h75.118@trnddc01>


swapnil.kale_at_gmail.com wrote:
> Hi there,
>
> I wish to insert two values as 0 if there exists a similar record in
> the database. Here goes the code for the same.
>
> The trigger is like this:
>
> CREATE OR REPLACE TRIGGER BEFORE_CHANGE_SVM
> BEFORE INSERT OR UPDATE
> ON ST_VOL_MNTH
> FOR EACH ROW
> REC := RY_CGE(:NEW.X,:NEW.Y,:NEW.Z);
> --the function is defined below.
> IF (REC>0) THEN
> -set the new value.abc :=0;
> END IF;
> END;
>
>
> Where the function is like this. which returns the number of records
> present in the table matching the criterea,
>
> SELECT COUNT(*) INTO REC FROM ST_VOL_MNTH SVM
> where values are matching.
>
> I'm getting the mutation error as i'm trying to query the same table on
> which the trigger is written.
> It would be great if somebody can help!
>
> Thanks in advance.
> Swapnil Kale.
>

There is a white paper in metalink published by Arup Nanda which explains the workaround in good detail. He is pretty good at what he writes. He was the "DBA of the Year" by Oracle Magazine in 2003 or 2004. The developers at our site use that workaround and it has been working very well. Received on Sat Feb 03 2007 - 21:49:18 CST

Original text of this message

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