Mutating Triggers

This post gives an introduction to mutating triggers and was tested on an Oracle 9 database. It contains points which I do not fully understand yet. When I do, I will come back and update it. First, create a table and add some data to it:

 

SQL> CREATE TABLE andrews_table

  2   (employee VARCHAR2(10))

  3  /

 

Table created.

 

SQL> INSERT INTO andrews_table

  2   VALUES ('SMITH')

  3  /

 

1 row created.

 

SQL> INSERT INTO andrews_table

  2   VALUES ('JONES')

  3  /

 

1 row created.

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

Now create a mutating trigger. It fires for each row while the table is being modified so Oracle does not know what value to assign to employee:

 

SQL> CREATE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   FOR EACH ROW

  4   BEGIN

  5   UPDATE andrews_table

  6   SET employee = 'BLOGGS';

  7   END;

  8  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'BROWN'

  3  /

UPDATE andrews_table

       *

ERROR at line 1:

ORA-04091: table SYSTEM.ANDREWS_TABLE is

mutating, trigger/function may not see it

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

 

SQL>

 

So when you look at the data, it has not changed:

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

SQL>

 

If you remove FOR EACH ROW, the trigger appears to fire after the update and sets up an infinite loop until Oracle notices what has happened:

 

SQL> CREATE OR REPLACE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   BEGIN

  4   UPDATE andrews_table

  5   SET employee = 'GREEN';

  6   END;

  7  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'BLACK'

  3  /

UPDATE andrews_table

       *

ERROR at line 1:

ORA-00036: maximum number of recursive SQL levels

(50) exceeded

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.A

 

SQL>

 

And again, the data has not changed:

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

SQL>

 

Even if the trigger only reads the table, Oracle still says it is mutating:

 

SQL> CREATE OR REPLACE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   FOR EACH ROW

  4   DECLARE counter NUMBER;

  5   BEGIN

  6   SELECT COUNT(1) INTO counter FROM andrews_table;

  7   END;

  8  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'WHITE'

  3  /

UPDATE andrews_table

       *

ERROR at line 1:

ORA-04091: table SYSTEM.ANDREWS_TABLE is

mutating, trigger/function may not see it

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 3

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

 

SQL>

 

If you run oerr ora 04091 on the server, the output makes it clear that this is supposed to happen:

 

TEST9 > oerr ora 04091

04091, 00000, "table %s.%s is mutating, trigger/function may not see it"

// *Cause: A trigger (or a user defined plsql 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.

TEST9 >

 

And the data still has not changed:

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

SQL>

 

But if the trigger only reads the table and you remove FOR EACH ROW, Oracle fires the trigger once after the update, you do not set up an infinite loop and the update works:

 

SQL> CREATE OR REPLACE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   DECLARE counter NUMBER;

  4   BEGIN

  5   SELECT COUNT(1) INTO counter FROM andrews_table;

  6   END;

  7  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'KING'

  3  /

 

2 rows updated.

 

SQL>

 

And finally, the data has changed:

 

SQL> SELECT * FROM andrews_table;

 

EMPLOYEE

----------

KING

KING

 

SQL>