Re: Need workaround for Oracle Mutating table

Thanks DA Morgan. It solved the problem. Thanks a lot for the help. I referred the "Autonomous Transaction" and thats what i needed. <I'll definately think over remodelling it.>

Regards,
Swapnil

DA Morgan wrote:
> 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
Received on Sun Jan 21 2007 - 23:16:29 CST