Mutating Table Exceptions

Mutating table exceptions occur when we try to reference the triggering table in a query from within row-level trigger code. In this article I'll present and example of how a mutating table exception might occur and a simple method to get round it.

Let's assume we need to audit the actions on a table and record the total number of records when this audit record was created. We might have a schema like:

CREATE TABLE tab1 (
  id           NUMBER(10) NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

ALTER TABLE tab1 ADD (
  CONSTRAINT tab1_pk PRIMARY KEY (id)
);

CREATE SEQUENCE tab1_seq;


CREATE TABLE tab1_audit (
  id            NUMBER(10) NOT NULL,
  action        VARCHAR2(10) NOT NULL,
  tab1_id       NUMBER(10),
  record_count  NUMBER(10),
  created_time  TIMESTAMP
);

ALTER TABLE tab1_audit ADD (
  CONSTRAINT tab1_audit_pk PRIMARY KEY (id)
);

ALTER TABLE tab1_audit ADD (
  CONSTRAINT tab1_audit_tab1_fk FOREIGN KEY (tab1_id)
  REFERENCES tab1(id)
);

CREATE SEQUENCE tab1_audit_seq;

To following best practices we place all our trigger code into a package as follows:

CREATE OR REPLACE PACKAGE trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2);

END trigger_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2) IS
  l_count  NUMBER(10);
BEGIN
  SELECT COUNT(*)
  INTO   l_count
  FROM   tab1;

  INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
  VALUES (tab1_audit_seq.NEXTVAL, p_action, p_id, l_count, SYSTIMESTAMP);
END tab1_row_change;

END trigger_api;
/
SHOW ERRORS

Next we create the row-level trigger itself to catch any changes to the table:

CREATE OR REPLACE TRIGGER tab1_ariu_trg
AFTER INSERT OR UPDATE ON tab1
FOR EACH ROW
BEGIN
  IF inserting THEN
    trigger_api.tab1_row_change(p_id => :new.id, p_action => 'INSERT');
  ELSE
    trigger_api.tab1_row_change(p_id => :new.id, p_action => 'UPDATE');
  END IF;
END;
/
SHOW ERRORS

If we try to insert into the TAB1 table we might expect the insert to complete and the audit record to be created but as you can see below this is not the case:

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE');
INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE')
                                           *
ERROR at line 1:
ORA-04091: table TIM_HALL.TAB1 is mutating, trigger/function may not see it
ORA-06512: at "TIM_HALL.TRIGGER_API", line 7
ORA-06512: at "TIM_HALL.TAB1_ARIU_TRG", line 3
ORA-04088: error during execution of trigger 'TIM_HALL.TAB1_ARIU_TRG'

We can get round this issue by using a combination of row-level and statement-level triggers. First we alter the TRIGGER_API package to store any data passed by the row-level trigger in a PL/SQL table. We also add a new statement-level procedure to process each of the rows in the PL/SQL table:

CREATE OR REPLACE PACKAGE trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2);

PROCEDURE tab1_statement_change;

END trigger_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY trigger_api AS

TYPE t_change_rec IS RECORD (
  id      tab1.id%TYPE,
  action  tab1_audit.action%TYPE
);

TYPE t_change_tab IS TABLE OF t_change_rec;
g_change_tab  t_change_tab := t_change_tab();

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2) IS
BEGIN
  g_change_tab.extend;
  g_change_tab(g_change_tab.last).id     := p_id;
  g_change_tab(g_change_tab.last).action := p_action;
END tab1_row_change;

PROCEDURE tab1_statement_change IS
  l_count  NUMBER(10);
BEGIN
  FOR i IN g_change_tab.first .. g_change_tab.last LOOP
    SELECT COUNT(*)
    INTO   l_count
    FROM   tab1;

    INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
    VALUES (tab1_audit_seq.NEXTVAL, g_change_tab(i).action, g_change_tab(i).id, l_count, SYSTIMESTAMP);
  END LOOP;
  g_change_tab.delete;
END tab1_statement_change;

END trigger_api;
/
SHOW ERRORS

Our existing row-level trigger is fine, but we need to create a statement-level trigger to call our new procedure:

CREATE OR REPLACE TRIGGER tab1_asiu_trg
AFTER INSERT OR UPDATE ON tab1
BEGIN
  trigger_api.tab1_statement_change;
END;
/
SHOW ERRORS

The TAB1 inserts/updates will now work without mutation errors:

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE');

1 row created.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'TWO');

1 row created.

SQL> UPDATE tab1 SET description = description;

2 rows updated.

SQL> SELECT * FROM tab1;

        ID DESCRIPTION
---------- -----------
         2 ONE
         3 TWO

2 rows selected.

SQL> SELECT * FROM tab1_audit;

        ID ACTION        TAB1_ID RECORD_COUNT CREATED_TIME
---------- ---------- ---------- ------------ -------------------------
         1 INSERT              2            1 03-DEC-03 14.42.47.515589
         2 INSERT              3            2 03-DEC-03 14.42.47.600550
         3 UPDATE              2            2 03-DEC-03 14.42.49.178678
         4 UPDATE              3            2 03-DEC-03 14.42.49.179655

4 rows selected.

Note. The introduction of Compound Triggers in Oracle 11g Release 1 makes solving mutating table errors much simpler.

Hope this helps. Regards Tim...

Back to the Top.