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.

There is a reason why oracle throws a mutating table error. Consider a case when your session is entering the second row which it believes should set the value to 0 and another session is deleting that first row.

What happens then?

You could potentially workaround mutating table error using autonomous transactions. However, the real issue is of a bad design. You need to reconsider your design and for once imagine that a database can support concurrent transactions.

Your issue can more simply be fixed by using a view over that table which will return 0 for subsequent rows as per the required design.

Anurag Received on Thu Jan 18 2007 - 10:20:56 CST