Title: ORA-4091 on BEFORE ROW TRIGGER with INSERT statement
Content Type: TEXT/X-HTML
Create Date: 16-JAN-2001
Privious Modify Date: 09-AUG-2004
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.
INTO content (cont_name,cont_seg,cat_seq)
1 row created.
However, your trigger errors with ERROR ORA-4091 with
INSERT INTO…select statement:
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'
CREATE OR REPLACE trigger INS_CONTENT
BEFORE INSERT on CONTENT
FOR EACH ROW
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;
SELECT SEQ_CONT_SEQ.nextval INTO :new.CONT_SEQ from dual;
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.
You cannot look at or modify the table that is mutating.
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.
Oracle Application Developer's Guide (A68003-01)
Chapter 'Using Database Triggers', page 13-22)