In article <MPG.e07759eaa6e0a29989696_at_nsw-newshost.tpgi.com.au>,
Business Information Services <email_at_bisinfo.com.au> writes
>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.
Hi.
The workaround suggested my more than one poster is valid - it's also
documented in Pl/SQL Programming by Scott Urman (Oracle Press).
One point that I haven't seen raised in this thread is that you _can_ do
a read from the same table if all you're doing is an insert. You can't
even do that if you're updating.
Hope this helps a little.
Paul Brewer
Received on Tue Jun 17 1997 - 00:00:00 CDT
