Re: Mutating

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.. Received on Thu Jun 12 1997 - 00:00:00 CDT