Ora 4091

ID: note:132569.1
Title: ORA-4091 on BEFORE ROW TRIGGER with INSERT statement
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/X-HTML
Create Date: 16-JAN-2001
Privious Modify Date: 09-AUG-2004

Problem Description


You want to do an insert into a table that has a BEFORE row Trigger.

When you hard code the values into the INSERT statement, the trigger works fine.

For example:

INSERT
INTO content (cont_name,cont_seg,cat_seq)
VALUES('blah',100,200);

1 row created.

However, your trigger errors with ERROR ORA-4091 with
INSERT INTO…select statement:

INSERT
INTO content (cont_name,cont_seq,cat_seq) (select….from category);

ERROR at line 1:
ORA-4091: table <schema>.CONTENT is mutating, trigger/function may not see it
ORA-6512: at "<schema>.INS_CONTENT", line 4
ORA-4088: error during execution of trigger '<schema>.INS_CONTENT'

TRIGGER:

CREATE OR REPLACE trigger INS_CONTENT
BEFORE INSERT on CONTENT
FOR EACH ROW
DECLARE
max_sort number;
BEGIN
SELECT max(cont_sort) INTO max_sort FROM CONTENT;
IF max_sort IS NOT NULL AND max_sort!= 99999 THEN
IF :new.cont_sort IS NULL THEN
:new.cont_sort := max_sort +1;
END IF;
END IF;
SELECT SEQ_CONT_SEQ.nextval INTO :new.CONT_SEQ from dual;
END;

Explanation


Error: ORA 4091
Text: table %s.%s is mutating, trigger/function may not see it


Cause: A trigger (or a user defined PL/SQL function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

Explanation:
You cannot look at or modify the table that is mutating.

Note:
From the Application Developers Guide
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers.
INSERT statements that involve more than 1 row are not considered
single row inserts."

"INSERT INTO <table_name> SELECT …" are not considered single row
inserts, even if they only result in 1 row being inserted.

RELATED DOCUMENTS


Oracle Application Developer's Guide (A68003-01)
Chapter 'Using Database Triggers', page 13-22)

Ora-4091 cn