ORA-04091 Having a Statement TRIGGER On a Table With a Foreign Key Using "ON DELETE SET NULL" Option (Doc ID 1431406.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

"ORA-04091: table is mutating, trigger/function may not see it" returned
when a statement trigger is fired on a table with foreign key constraint
using the 'on delete set null'-option.

DROP TABLE testtab;

CREATE TABLE testtab
(id NUMBER NOT NULL,
status CHAR(1) NOT NULL,
origin_fk NUMBER);


ALTER TABLE testtab ADD CONSTRAINT testtab_pk PRIMARY KEY (id);


ALTER TABLE testtab
ADD CONSTRAINT testtab_fk FOREIGN KEY (origin_fk)
REFERENCES testtab (id) on delete set null;


CREATE OR REPLACE TRIGGER test_trig
AFTER
DELETE OR UPDATE
ON testtab
declare
v_count number := 0;
v_status char := '';
begin
Select status into v_status from testtab where id = 1;
v_count:=1;
end;
/

insert into testtab values (1,'N',NULL);
insert into testtab values (2,'N',1);
commit;


Steps to Reproduce:

1. Logon into the database and execute a DML statement to delete a row from the testatb table.

SQL> delete from testtab where id=1;
delete from testtab where id=1
            *
ERROR at line 1:
ORA-04091: table SCOTT.TESTTAB is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TEST_TRIG", line 5
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIG'

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms