Re: Avoiding "ORA-04091 table string.string is mutating"

Carlo Sirna wrote:
>
> Hi, maybe this is a faq but I didn't find any solution...
>
> I have a table like this one, which describes some time intervals in
> the form [startdate,enddate].
>
> create table mytab
> (
> startdate date not null,
> enddate date not null
> )
>
> I don't want this table to contain overlapping intervals: it must be
> impossible to insert a record that overlaps with an already existing
> one or to update an existing record in order to make it overlap over
> onto another one.
>
> I had no problems in writing a before insert trigger that performs the
> required test, but I don't know how this check could be performed in a
> before update trigger.
>
> p.s: this table contains about one hundred thousands of records: I
> can't keep it all in package variable...
>
> Any Ideas?
>
> Thanks in advance,
>
> Carlo Sirna

The basic mutating table avoidance is:

before-statement-trigger:
  clear a PL/SQL table

after-row:
  add rowid for this row to PL/SQL table

after-statement-trigger:
  use each rowid and do the work you would have liked to have done within the row level trigger

Easy enough - but if you have concurrent users modifying this table, you will need to use something like dbms_lock or a sentinel table to control access to the table whilst you do your work.

Connor Received on Tue Nov 04 2003 - 05:22:51 CST