Re: Error ORA-04091

Hi Luca,

You probably are trying to perform updates in a so-called row trigger (one declared with FOR EACH ROW clause of CREATE TRIGGER). Find out first if you need to update the table not for every row inserted,

but for every INSERT operation. If so, change the trigger def to exclude FOR EACH ROW (to be a statement trigger).

Oracle considers the table as being changed until all row triggers on all rows complete. If Oracle is executing the code inside of those row triggers,
it thinks that the next operation might change the table in some way, and your operation is therefore inconsistent. This applies even to SELECT statements. In a statement trigger all changes requested by an operation have been applied to rows.

If you need any data about rows, store them in PLSQL package variables.

Thomas Kyte has got an in-depth explanation of mutating tables at http://govt.us.oracle.com/~tkyte/. He describes all the techniques and looks like he covers all possible cases.

Rgrds,
Karen Abgarian.

Luca Cappelletti wrote:

> Hi!
> Is there anybody who knows this error?
> I'm calling a stored procedure from an "AFTER INSERT" trigger. The SP
> open a cursor and modifies a lot of records of the same table invoking
> the trigger.
>
> The error code and decription are: "ORA-04091: table MYSCHEMA.MYTABLE
> is mutating, trigger/function may not see it"
>
> Help me!
> Thanks in advance.
> Luca
Received on Thu Aug 19 1999 - 08:37:07 CDT