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:


  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 I have read about compound triggers but the sample does not use them.

  • 1
    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.
    – user1822
    Nov 25, 2012 at 12:06
  • 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.
    – Centurion
    Nov 25, 2012 at 12:09
  • This sure is strange. Btw: here is an SQLFiddle example sqlfiddle.com/#!4/9e59f/2
    – user1822
    Nov 25, 2012 at 12:15
  • Thanks for sharing info, cool link. Didn't know there's such Oracle SQL testing website :)
    – Centurion
    Nov 25, 2012 at 12:21
  • a_horse_with_no_name: Another example: Fiddle-test-2 (SET salary=salary+10) Nov 25, 2012 at 12:46

1 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'
  • That does seem to be the culprit. Do you have an reference in the manual for this behaviour?
    – user1822
    Nov 25, 2012 at 12:52
  • @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). Nov 25, 2012 at 12:56
  • 2
    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.
    – user1822
    Nov 25, 2012 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.