Re: Mutating

From: Willem Gestel <willem.gestel_at_tip.nl>
Date: 1997/06/15
Message-ID: <01bc79b4$5fcd5e60$524e12c3@csollt50>#1/1

This is a multi-part message in MIME format.

------=_NextPart_000_01BC79C5.23562E60
Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

Hello,

You can't read values out of a table where the trigger is on. Try using a column-constraint. That should do it...

Good luck,

Willem Gestel
Deventer
The Netherlands

Business Information Services <email_at_bisinfo.com.au> wrote in article <MPG.e07759eaa6e0a29989696_at_nsw-newshost.tpgi.com.au>...
> 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
>

------=_NextPart_000_01BC79C5.23562E60
Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

<html><head></head><BODY bgcolor=3D"#FFFFFF">Hello, You can't read values out = of a table where the trigger is on. Try using a column-constraint. That = should do it... Good luck, Willem = Gestel Deventer The Netherlands Business = Information Services <<font =

color=3D"#0000FF"><u>email_at_bisinfo.com.au</u><font =
color=3D"#000000">&gt; wrote in article &lt;<font =
color=3D"#0000FF"><u>MPG.e07759eaa6e0a29989696_at_nsw-newshost.tpgi.com.au</=

u>>... > 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) > =

&nbsp;&nbsp;&nbsp;&nbsp;INTO MAX_DATE<br>&gt; =
&nbsp;&nbsp;&nbsp;&nbsp;FROM FILLER_RUN<br>&gt; =
&nbsp;&nbsp;&nbsp;&nbsp;WHERE RUN_FK =3D :NEW.RUN_FK;<br>&gt; <br>&gt; =
&nbsp;&nbsp;&nbsp;&nbsp;... if .. then etc.<br>&gt; END;<br>&gt; =

> 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) > <font =

color=3D"#0000FF"><u>mailto:email_at_bisinfo.com.au</u><font =
color=3D"#000000"><br>&gt; <font =
color=3D"#0000FF"><u>http://www.bisinfo.com.au</u><font =
color=3D"#000000"><br>&gt; </p>

</body></h= tml>
------=_NextPart_000_01BC79C5.23562E60-- Received on Sun Jun 15 1997 - 00:00:00 CDT