ORA-04091 Having a Statement TRIGGER On a Table With a Foreign Key Using "ON DELETE SET NULL" Option
Last updated on JULY 05, 2017
Applies to:PL/SQL - Version 184.108.40.206 and later
Information in this document applies to any platform.
"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,
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
DELETE OR UPDATE
v_count number := 0;
v_status char := '';
Select status into v_status from testtab where id = 1;
insert into testtab values (1,'N',NULL);
insert into testtab values (2,'N',1);
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'
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms