Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

1 Answer 1

up vote 11 down vote accepted

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'
share|improve this answer
    
That does seem to be the culprit. Do you have an reference in the manual for this behaviour? –  a_horse_with_no_name Nov 25 '12 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). –  Nicholas Krasnov Nov 25 '12 at 12:56
    
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. –  a_horse_with_no_name Nov 25 '12 at 13:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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