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: 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"><p><font size=3D2 = color=3D"#000000" face=3D"Arial">Hello,<br><br>You can't read values out = of a table where the trigger is on. Try using a column-constraint. That = should do it...<br><br>Good luck,<br><br>Willem = Gestel<br>Deventer<br>The Netherlands<br><br><br><br>Business = Information Services &lt;<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><font color=3D"#000000">&gt;...<br>&gt; Greeting all!<br>&gt; <br>&gt; = We have a before insert and update trigger set up on a table called = <br>&gt; &quot;FILLER_RUN&quot; with the following code.<br>&gt; = <br>&gt; DECLARE<br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;MAX_DATE = &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;date;<br>&gt; BEGIN<br>&gt; = <br>&gt; &nbsp;&nbsp;&nbsp;&nbsp;SELECT MAX(EFFECTIVE_FROM)<br>&gt; =
&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; =
<br>&gt; A &quot;run&quot; can have many &quot;effective from&quot; = dates. The purpose of this query <br>&gt; is to find the current maximum = effective date for a run which we then <br>&gt; compare with the date = the user is trying to enter. If it is less than or <br>&gt; equal to the = max date for that run then we raise an exception.<br>&gt; <br>&gt; We = are getting a mutating table error but we thought that this only = <br>&gt; occured when you tried to change something in the trigger. All = we are <br>&gt; doing is reading from the database.<br>&gt; <br>&gt; Can = anyone help out?<br>&gt; <br>&gt; Regards,<br>&gt; Peter.<br>&gt; -- = <br>&gt; Business Information Services<br>&gt; (For more information = call Brad Deveson)<br>&gt; <br>&gt; Tel: (02) 9387-2509 = (Australia)<br>&gt; Fax: (02) 9369-3840 (Australia)<br>&gt; <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>

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

Original text of this message

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