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: Mutating

Re: Mutating

From: Vijay Darekar <vijayd_at_worldnet.att.net>
Date: 1997/06/12
Message-ID: <33A0E1CB.356C@worldnet.att.net>#1/1

jean claude wrote:
>
> Business Information Services wrote:
> >
> > Greeting all!
> >
> > We have a before insert and update trigger set up on a table called
> > "FILLER_RUN" with the following code.
> >
> > DECLARE
> > MAX_DATE date;
> > BEGIN
> >
> > SELECT MAX(EFFECTIVE_FROM)
> > INTO MAX_DATE
> > FROM FILLER_RUN
> > WHERE RUN_FK = :NEW.RUN_FK;
> >
> > ... if .. then etc.
> > END;
> >
> > A "run" can have many "effective from" dates. The purpose of this query
> > is to find the current maximum effective date for a run which we then
> > compare with the date the user is trying to enter. If it is less than or
> > equal to the max date for that run then we raise an exception.
> >
> > We are getting a mutating table error but we thought that this only
> > occured when you tried to change something in the trigger. All we are
> > doing is reading from the database.
> >
> > Can anyone help out?
> >
> > Regards,
> > Peter.
> > --
> > Business Information Services
> > (For more information call Brad Deveson)
> >
> > Tel: (02) 9387-2509 (Australia)
> > Fax: (02) 9369-3840 (Australia)
> > mailto:email_at_bisinfo.com.au
> > http://www.bisinfo.com.au
>
> I had the same problem on Oracle 7.1.
> The trigger are very restrictive : you can read in the documentation that you can not
> query (select) the table you have triggered;
> you can not query the referenced tables : your table can have foreign keys on others
> tables, you can not query their.
>
> The only way to know is a trigger is OK : fire it.
>
> Perhaps new release of Oracle haven't such problem ? i don't know.
> Good luck..

It is true that you can't reference the table in trigger which is for the row level but in the statement level trigger you can reference it. To solve the mutating error you have to get around it.

One solution is declare the PL/SQL table of type "FILLER_RUN" in the pakcage header.

In the before insert/update row level trigger store the primary key information into the PL/SQL table.

In the after statement trigger you can update or refer the "FILLER_RUN" table.

Let me know if you have problem in solving the mutation. Received on Thu Jun 12 1997 - 00:00:00 CDT

Original text of this message

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