Oracle: "table is mutating, trigger/function may not see it"

Question

(Yes, this is one of those question again...)

I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server.

I have a table I1 (called t_link in my previous question) referencing two other tables A and B (previsouly called t_parent and t_child) and want to delete rows in I1 when both references to A and B are NULL (see the addendum below for more information about why).

CREATE TABLE "A" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
        PRIMARY KEY NOT NULL);

CREATE TABLE "B" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
        PRIMARY KEY NOT NULL, 
    "ID_A" int,
     FOREIGN KEY ("ID_A") REFERENCES "A"("ID") ON DELETE SET NULL);

CREATE TABLE "I1" (
    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
        PRIMARY KEY NOT NULL, 
    "ID_A" int, 
    "ID_B" int,
    FOREIGN KEY ("ID_A") REFERENCES "A"("ID") ON DELETE SET NULL,
    FOREIGN KEY ("ID_B") REFERENCES "B"("ID") ON DELETE SET NULL);

CREATE OR REPLACE TRIGGER TRG_DELNULLS_A_I1_ID_A
BEFORE DELETE ON "A"
FOR EACH ROW
BEGIN
    DELETE FROM "I1" WHERE "ID_B" IS NULL AND "ID_A" = :OLD."ID";
END;
/

CREATE OR REPLACE TRIGGER TRG_DELNULLS_B_I1_ID_B
BEFORE DELETE ON "B"
FOR EACH ROW
BEGIN
    DELETE FROM "I1" WHERE "ID_A" IS NULL AND "ID_B" = :OLD."ID";
END;
/

Then I insert data:

INSERT INTO "A" VALUES (NULL);

SELECT * FROM "A";
   ID 
_____
    1

INSERT INTO "B" VALUES (NULL, 1);

SELECT * FROM "B";
   ID    ID_A 
_____ _______ 
    1       1 

INSERT INTO "I1" VALUES (NULL, 1, NULL);
INSERT INTO "I1" VALUES (NULL, NULL, 1);
INSERT INTO "I1" VALUES (NULL, 1, 1);

SELECT * FROM "I1";
   ID    ID_A    ID_B 
_____ _______ _______ 
    1       1         
    2               1 
    3       1       1 

Then I delete row A of ID 1 and expect that I1 of ID 1 would be deleted but not ID 3. That's where get the ORA-04091:

DELETE FROM "A" WHERE "ID" = 1;

Error starting at line : 1 in command -
DELETE FROM "A" WHERE "ID" = 1
Error report -
ORA-04091: table C##USER.I1 is mutating, trigger/function may not see it
ORA-06512: at "C##USER.TRG_DELNULLS_A_I1_ID_A", line 2
ORA-04088: error during execution of trigger
'C##USER.TRG_DELNULLS_A_I1_ID_A'

... which I don't understand because other questions related to the "table is mutating" error usually explicitely manipulate the trigger table in the trigger body; which I'm not doing (at least not explicitely).

The same kind of trigger works fine with Postgres and I was hoping to be able to use constraints unlike with MS SQL Server so I guess there must be a tiny statement I missed somewhere (or so I hope)...

---

Addendum:

Basically, items from I1 are measurements (e.g. CPU, RAM, disk space) made at a different time in different timelines (A and B being the timelines). I1 contains two timestamp columns tsA and tsB that I omitted for brevity. As there can be several millions rows of I1 (and there are around 10 other tables like I1) I thought it would be more efficient to put those values in a single row instead of having two rows (after all, it's the same measurement). The software in charge of the database needs to handle several types of DBMS (depending on the customer): we started out with Postgres and I had to add MS SQL Server support and now Oracle, so I will be sacrificing (a bit of) performance for code simplicity...

Having faced the same problem with MS SQL Server, I solved it by completely removing the foreign key constraints and putting all the logic (including the potential deletions of the foreign rows) in an AFTER DELETE trigger.

I was hoping that Oracle would be different. I'm not a DB person and have a limited understanding on what runs under the hood when a delete is performed (transaction, triggers call order, reentrancy, ...).

Answer 1

Well, I tried with an AFTER DELETE trigger (that showed the same problem), an INSTEAD OF DELETE trigger (ORA-25002: forbidden on tables), and dropping the ON DELETE constraints (ORA-02292: child records found).

I'm ashamed to say I found a way out: same as for MS SQL Server, I dropped all FOREIGN KEY constraints and do all of the logic in two BEFORE DELETE triggers (I guess AFTER DELETE would work as well):

CREATE TABLE "A" ("ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY NOT NULL);

CREATE TABLE "B" ("ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY NOT NULL, "ID_A" int);

The ON DELETE SET NULL equivalent trigger:

CREATE OR REPLACE TRIGGER TRG_DELNULLS_A_B_ID_A
BEFORE DELETE ON "A"
FOR EACH ROW
BEGIN
    UPDATE "B" SET "ID_A" = NULL WHERE "ID_A" = :OLD."ID";
END;
/

CREATE TABLE "I1" ("ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY NOT NULL, "ID_A" int, "ID_B" int);

The ON DELETE SET NULL equivalent triggers, promoted to CASCADE if the other ID_[AB] is null:

CREATE OR REPLACE TRIGGER TRG_DELNULLS_A_I1_ID_A
AFTER DELETE ON "A"
FOR EACH ROW
BEGIN
    DELETE FROM "I1" WHERE "ID_B" IS NULL AND "ID_A" = :OLD."ID";
    UPDATE "I1" SET "ID_A" = NULL WHERE "ID_A" = :OLD."ID";
END;
/

CREATE OR REPLACE TRIGGER TRG_DELNULLS_B_I1_ID_B
BEFORE DELETE ON "B"
FOR EACH ROW
BEGIN
    DELETE FROM "I1" WHERE "ID_A" IS NULL AND "ID_B" = :OLD."ID";
    UPDATE "I1" SET "ID_B" = NULL WHERE "ID_B" = :OLD."ID";
END;

After the inserts, deletions work as I want:

DELETE FROM "A" WHERE "ID" = 1;

1 row deleted.

SELECT * FROM b;

   ID    ID_A 
_____ _______ 
    1         

(B.ID_A is null)

SELECT * FROM "I1";

   ID    ID_A    ID_B 
_____ _______ _______ 
    2               1 
    3               1 

(Row 1 with a null ID_B was deleted, row 3 with a non-null ID_B is kept but its ID_A that was 1 is now null)

DELETE FROM "B" WHERE "ID" = 1;

1 row deleted.

SELECT * FROM "I1";

no rows selected

(Both remaining I1 are deleted because their ID_A were null and their ID_B was corresponding to the B row that is being deleted).

Am I proud of that? Absolutely not, but at least it works as I want. I'll be very happy to accept any other answer that can make use of integrity constraints or better-designed triggers...