Re: Need workaround for Oracle Mutating table

On Jan 18, 9:12 am, swapnil.k..._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.

If you have Oracle support you can go to metalink and look for a white paper on this subject that uses a statement level trigger along with the row level trigger to work around mutating table errors. The developers I work with have not had much luck using the technique. You can also search the asktom site on this as Tom has an example of the work around.

In case where there is only one or two valid sources for inserts or updates as the case may be I ususally suggest using a stored procedure to perform the update process. The procedure can select from the table prior to performing the insert. Usually a row level trigger, check constraint, or even not null constraint can be used to ensure that the logic was performed prior to the insertion.

HTH -- Mark D Powell -- Received on Thu Jan 18 2007 - 08:50:19 CST