(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
t_link in my previous question) referencing two other tables
B (previsouly called
t_child) and want to delete rows in
I1 when both references to
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)...
Basically, items from
I1 are measurements (e.g. CPU, RAM, disk space) made at a different time in different timelines (
B being the timelines).
I1 contains two timestamp columns
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, ...).