Mutating Table Error on Cascading Delete

Christoph Gächter, Akadia AG, Information Technology, CH-3604 Thun
Phone: +41 33 335 86 21 / Fax: +41 33 335 86 25 /
EMail: christoph.gaechter@akadia.com

Suppose you have master / detail tables using referential integrity constraints with cascading delete. If the application deletes the master record, the database will delete automatically all the detail records.

The example below introduces a logging mechanism to write out some master / detail information during the deletion of the master record.

The example uses the well known tables DEPT (the master table) and EMP (the detail table): Every employee has exactly one department assigned. One department has no, one, or more employees.

SELECT deptno, dname FROM dept;

   DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SELECT empno, ename, deptno FROM emp;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

The original tables DEPT and EMP are using referential integrity without cascading delete. Therefore change the foreign key constraint as follows:

ALTER TABLE emp DROP CONSTRAINT fk_dept_deptno;
ALTER TABLE emp ADD (
CONSTRAINT fk_dept_deptno
    FOREIGN KEY (deptno)
    REFERENCES dept (deptno) ON DELETE CASCADE
);

Now if the application deletes e.g. department 10, all its dependent employee records will be deleted automatically by cascading delete:

DELETE FROM dept WHERE deptno = 10;
1 row deleted.

SELECT empno, ename, deptno FROM emp;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7788 SCOTT              20
      7844 TURNER             30
      7876 ADAMS              20
      7900 JAMES              30
      7902 FORD               20

In a first approach, a row trigger on table DEPT is used. It will log all the master / detail information on the record, which will be deleted. This will force Oracle to throw a mutating table error. In a second approach, we use a view on table DEPT and a delete trigger on this view.

A first approach to write out the master / detail information according to the deletion of the master record is a delete trigger on table DEPT. The code might look something like this:

CREATE OR REPLACE TRIGGER trg_dept_delete
BEFORE DELETE ON dept FOR EACH ROW
DECLARE
CURSOR get_emp( p_deptno NUMBER ) IS
    SELECT empno, ename, job, sal, comm
      FROM emp
     WHERE deptno = p_deptno;
BEGIN
dbms_output.put_line( 'Delete dept = ' || :old.deptno );
dbms_output.put_line( '- dept name = ' || :old.dname );
dbms_output.put_line( '- dept loc = ' || :old.loc );
FOR get_emp_rec IN get_emp( :old.deptno ) LOOP
    dbms_output.put( '- emp ( ' || get_emp_rec.empno );
    dbms_output.put( ', ' || get_emp_rec.ename );
    dbms_output.put( ', ' || get_emp_rec.job );
    dbms_output.put( ', ' || get_emp_rec.sal );
    dbms_output.put( ', ' || get_emp_rec.comm );
    dbms_output.put_line( ' )' );
END LOOP;
END;
/

The main focus is the output of department and employee information in the loop. The package dbms_output is only used to keep the example small and runnable everywhere. In real life you would use your own logging packages.

Now we are ready to delete department 10 again:

DELETE FROM dept WHERE deptno = 10;
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG_DEPT_DELETE", line 3
ORA-06512: at "SCOTT.TRG_DEPT_DELETE", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_DEPT_DELETE'

Oops, the same delete activity fails because of cascading delete and the previous logging of deleting information. Oracle throws a mutating table error during the row deletion of the master record.

Remove the obsolete trigger to avoid confusion with further examples:

DROP TRIGGER trg_dept_delete;

A second approach to write out master / detail information according to deletion of the master record is the usage of a view. Instead of working directly with the table DEPT, we create and use a view V_DEPT without restrictions. On this view we create a delete trigger. This trigger will write out all the master / detail information.

CREATE VIEW v_dept AS SELECT * FROM dept;

CREATE OR REPLACE TRIGGER trg_v_dept_delete
INSTEAD OF DELETE ON v_dept
DECLARE
CURSOR get_emp( p_deptno NUMBER ) IS
    SELECT empno, ename, job, sal, comm
      FROM emp
     WHERE deptno = p_deptno;
BEGIN
dbms_output.put_line( 'Delete dept = ' || :old.deptno );
dbms_output.put_line( '- dept name = ' || :old.dname );
dbms_output.put_line( '- dept loc = ' || :old.loc );
FOR get_emp_rec IN get_emp( :old.deptno ) LOOP
    dbms_output.put( '- emp ( ' || get_emp_rec.empno );
    dbms_output.put( ', ' || get_emp_rec.ename );
    dbms_output.put( ', ' || get_emp_rec.job );
    dbms_output.put( ', ' || get_emp_rec.sal );
    dbms_output.put( ', ' || get_emp_rec.comm );
    dbms_output.put_line( ' )' );
END LOOP;
END;
/

SHOW ERROR;

Now the application can delete the master record in the V_DEPT view and the database will log the master / detail information:

DELETE FROM v_dept WHERE deptno = 10;

Delete dept = 10
- dept name = ACCOUNTING
- dept loc = NEW YORK
- emp ( 7782, CLARK, MANAGER, 2450, )
- emp ( 7839, KING, PRESIDENT, 5000, )
- emp ( 7934, MILLER, CLERK, 1300, )

1 row deleted.

This solution works fine! Using (private) synonyms for applications, table DEPT could be hidden behind the view V_DEPT. In this case no changes are needed in the application's code.

Again remove view and trigger for further examples:

DROP TRIGGER trg_v_dept_delete;
DROP VIEW V_DEPT;

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.

With active referential integrity and cascading delete you can not access the detail records during the deletion of the master record. A possible workaround is to define a view on the master table and a delete trigger on this view. In this case it is possible to access master / detail information during deletion of the master record in a view.

Further articles about mutating tables:
https://www.akadia.com/services/ora_mutating_table_problems.html

Oracle Documentation:
Oracle Application Developer's Guide: Using Triggers
Oracle Concepts: Triggers