ORA-04091 table is mutating trigger/function may not see it when cascading, works otherwise

Question

I have the following two tables:

CREATE TABLE entries
(
    id                  NUMBER(20, 0)                           NOT NULL PRIMARY KEY,        
    first_name          VARCHAR2(512) DEFAULT NULL              NULL,
    last_name           VARCHAR2(512) DEFAULT NULL              NULL,
    last_updated_at     TIMESTAMP     DEFAULT NULL              NULL
);

CREATE TABLE entry_details
(
    id       NUMBER(20, 0)              NOT NULL PRIMARY KEY,
    entry_id NUMBER(20, 0)              NOT NULL,
    value    VARCHAR2(512) DEFAULT NULL NULL,
    CONSTRAINT fk_details_entry_id FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
);

I need to update last_updated_at whenever there's a change/delete in the entries or entry_details table, so I have this trigger:

CREATE OR REPLACE TRIGGER detail_delete_trigger
    BEFORE DELETE
    ON entry_details
    FOR EACH ROW
BEGIN
    UPDATE entries
    SET last_updated_at = CURRENT_TIMESTAMP
    WHERE id = :OLD.entry_id;
END;

This works fine when there's a delete from entry_details directly. However, the I get the ORA-04091: entries table is mutating error when there's delete from entries table which cascades to entry_details. I have tried:

1. PRAGMA AUTONOMOUS_TRANSACTIONS which yield to ORA-00060: deadlock detected while waiting for resource
2. Using a AFTER DELETE trigger, which gave again, the table is mutating error
3. Could not use INSTEAD OF DELETE because it's not for tables as per ORA-25002: cannot create INSTEAD OF triggers on tables
4. I removed CASCADE and added the following trigger:

ALTER TABLE entry_details DROP CONSTRAINT fk_details_entry_id;
ALTER TABLE entry_detailsADD CONSTRAINT fk_details_entry_idFOREIGN KEY (entry_id) REFERENCES entries(id);

CREATE OR REPLACE TRIGGER aml_entries_delete_trigger
    BEFORE DELETE -- or AFTER DELETE, same result
    ON entries
    FOR EACH ROW
BEGIN
    DELETE FROM entry_details WHERE entry_id = :OLD.id;
END;

But I still get the ORA-04091 table is mutating trigger/function may not see error. How do I overcome this?

Answer 1

Maybe you can use a flag that is set when a row in the parent table is deleted and unset after its deletions. You can try the following code:

CREATE TABLE entries
(
    id                  NUMBER(20, 0)                           NOT NULL PRIMARY KEY,        
    first_name          VARCHAR2(512) DEFAULT NULL              NULL,
    last_name           VARCHAR2(512) DEFAULT NULL              NULL,
    last_updated_at     TIMESTAMP     DEFAULT NULL              NULL
);

CREATE TABLE entry_details
(
    id       NUMBER(20, 0)              NOT NULL PRIMARY KEY,
    entry_id NUMBER(20, 0)              NOT NULL,
    value    VARCHAR2(512) DEFAULT NULL NULL,
    CONSTRAINT fk_details_entry_id FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
);

create or replace package trigger_state
as
    cascading BOOLEAN := FALSE;
end;
/


CREATE OR REPLACE TRIGGER entry_before_delete_trigger
    BEFORE DELETE
    ON entries
    FOR EACH ROW
BEGIN
    trigger_state.cascading := TRUE;
END;
/

CREATE OR REPLACE TRIGGER entry_after_delete_trigger
    AFTER DELETE
    ON entries
    FOR EACH ROW
BEGIN
    trigger_state.cascading := FALSE;
END;
/


CREATE OR REPLACE TRIGGER detail_delete_trigger
    BEFORE DELETE
    ON entry_details
    FOR EACH ROW
BEGIN
    IF NOT trigger_state.cascading THEN
        UPDATE entries
        SET last_updated_at = CURRENT_TIMESTAMP
        WHERE id = :OLD.entry_id;
        END IF;
END;
/

with testdata

insert into entries(id) values(1);
insert into entries(id) values(2);
insert into entries(id) values(3);
insert into entries(id) values(4);
insert into entry_details(id,entry_id) values(1,1);
insert into entry_details(id,entry_id) values(2,1);
insert into entry_details(id,entry_id) values(3,3);
insert into entry_details(id,entry_id) values(4,3);
insert into entry_details(id,entry_id) values(5,3);
insert into entry_details(id,entry_id) values(6,4);
insert into entry_details(id,entry_id) values(7,4);
commit;

Now try a delete

delete from entry_details where id=6;
commit;

This will update the parent row.