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)