Why am I NOT getting a mutating table error in trigger?

Question

It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation:

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

However, I cannot understand why this demo trigger is not failing with a "mutating table" error when I perform an insert into emp using SQL Developer or SQL*Plus:

CREATE OR REPLACE TRIGGER emp_bri 
 BEFORE INSERT ON emp 
 FOR EACH ROW
BEGIN
 SELECT max(id) + 1 INTO :NEW.id FROM emp;
 UPDATE emp SET salary = 5000;
END emp_bri;

The insertion completes successfully with the next id value and updates all emp records. I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0. I have read about compound triggers but the sample does not use them.

• user1822: Unrelated to your question, but: do NOT use select max(id) to assign unique numbers. Just don't. It's simply incorrect and it won't scale as well.
• Centurion: Yes, I know that :) The example is probably not very good in this case... Autoincrement values should be definitely implemented by using sequences and triggers.
• user1822: This sure is strange. Btw: here is an SQLFiddle example sqlfiddle.com/#!4/9e59f/2
• Centurion: Thanks for sharing info, cool link. Didn't know there's such Oracle SQL testing website :)
• ypercubeᵀᴹ: a_horse_with_no_name: Another example: Fiddle-test-2 (SET salary=salary+10)

Answer 1

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
 2 col1 number,
 3 col2 number
 4 )
 5 ;
Table created
SQL> create or replace trigger TR_TB_TR_TEST
 2 before insert on TB_TR_TEST
 3 for each row
 4 begin
 5 SELECT max(col1) + 1 INTO :NEW.col1
 6 FROM TB_TR_TEST;
 7 UPDATE TB_TR_TEST SET col2 = 5000;
 8 end;
 9 /
Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);
1 row inserted
SQL> insert into TB_TR_TEST(col1, col2) values(3,5);
1 row inserted
SQL> commit;
Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
 2 select 1, 2
 3 from dual;
insert into TB_TR_TEST(col1, col2)
 select 1, 2
 from dual
ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'

• user1822: That does seem to be the culprit. Do you have an reference in the manual for this behaviour?
• Nicholas Krasnov: @a_horse_with_no_name if you have access to the support.oracle.com, search for ID 132569.1 (ORA-4091 on BEFORE ROW TRIGGER with INSERT .. into SELECT statement).
• user1822: Thanks. Interesting enough this exception seems to be only documented in the 8i (!) manuals: docs.oracle.com/cd/F49540_01/DOC/server.815/a68003/… (the section ""Mutating and Constraining Tables") I can't find that statement in the current manuals.