Re: Need workaround for Oracle Mutating table

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.

What creates a mutating table error changes with the version so read the docs at tahiti.oracle.com. But most likely this can be solved by creating the function as an autonomous transaction.

Go to Morgan's Library at www.psoug.org and scroll down to 'Autonomous Transaction'.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org