An Issue
If You have foreign keys in Your database (and chances are huge, if You're using a relational database like Oracle properly 😎) which have ON DELETE CASCADE or ON DELETE SET NULL set, be aware that cascade deletion or records or setting a null value is also activating table triggers on child tables.
And this can cause a famous mutation ORA-04091 error if a parent table is referenced in child table triggers!
More about this error:
https://docs.oracle.com/en/error-help/db/ora-04091/?r=23ai
But why is that so?
I mean, according to the documentation, the mutation occurs when the table iteslf is referenced in the trigger and the DML transaction is still in progress... so the data in the table is not in the final state and the table is "mutating".
And in the scenario I mentioned a parent table is referenced in the trigger, not the child table.
The explanation is the following - because of the foreign key constraint and the cascade delete, the data in both parent and child tables is changing within the same transaction and not only the data in the child table where the trigger is located.
Consequently, both tables are still mutating and the error occurs.
An Example
There are 3 tables joined with foreign keys:
- financial periods DEMO_FIN_PERIODS
- invoices DEMO_FIN_INVOICES
- invoice lines DEMO_FIN_INVOICE_LINES
There is a trigger on DEMO_FIN_INVOICE_LINES table, which prevents altering or deleting invoice lines if the finance period is locked.
Scripts to create tables:
CREATE TABLE DEMO_FIN_PERIODS (
period_id number not null primary key,
year_month varchar2(7),
lock_period_yn varchar2(1)
);
CREATE TABLE DEMO_FIN_INVOICES (
invoice_id number not null primary key,
invoice_date date,
invoice_location varchar2(100),
period_id number,
constraint demo_fin_invoice_period_fk FOREIGN KEY (period_id) REFERENCES demo_fin_periods(period_id)
);
CREATE TABLE DEMO_FIN_INVOICE_LINES (
invoice_line_id number not null primary key,
invoice_id number not null,
item varchar2(100),
price number(10, 2),
constraint demo_fin_invoice_lines_fk FOREIGN KEY (invoice_id)
REFERENCES demo_fin_invoices(invoice_id)
ON DELETE CASCADE
);
Populate data in tables:
Insert into DEMO_FIN_PERIODS
(PERIOD_ID, YEAR_MONTH, LOCK_PERIOD_YN)
Values
(1, '202401', 'Y');
Insert into DEMO_FIN_INVOICES
(INVOICE_ID, INVOICE_DATE, INVOICE_LOCATION, PERIOD_ID)
Values
(1, TO_DATE('22.12.2024', 'DD.MM.YYYY'), 'Ljubljana', 1);
Insert into DEMO_FIN_INVOICE_LINES
(INVOICE_LINE_ID, INVOICE_ID, ITEM, PRICE)
Values
(1, 1, 'bread', 2.5);
Insert into DEMO_FIN_INVOICE_LINES
(INVOICE_LINE_ID, INVOICE_ID, ITEM, PRICE)
Values
(2, 1, 'milk', 1.2);
Create lock trigger on invoice lines:
CREATE OR REPLACE TRIGGER demo_fin_invoice_lin_trg_lock
BEFORE INSERT OR UPDATE OR DELETE ON demo_fin_invoice_lines
FOR EACH ROW
DECLARE
l_locked_yn demo_fin_periods.lock_period_yn%TYPE;
BEGIN
SELECT dfp.lock_period_yn
INTO l_locked_yn
FROM
demo_fin_invoices i --referencing invoices table!!!
JOIN demo_fin_periods dfp ON i.period_id = dfp.period_id
WHERE i.invoice_id = nvl(:new.invoice_id, :old.invoice_id);
if l_locked_yn = 'Y' then
RAISE_APPLICATION_ERROR(-20001, 'Period is locked!');
end if;
END;
An Error
If we try to delete an invoice for a locked financial period we expect a custom error "Period is locked!" to be produced from a trigger.
DELETE FROM demo_fin_invoices
WHERE invoice_id = 1;
But instead of a custom error, a mutating trigger error occurs and it is mentioning DEMO_FIN_INVOICES table, not DEMO_FIN_INVOICE_LINES table:
[Error] Execution (87: 13): ORA-04091: table HR.DEMO_FIN_INVOICES is mutating, trigger/function may not see it
ORA-06512: at "HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK", line 4
ORA-04088: error during execution of trigger 'HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK'
Solutions for this scenario
Separate transactions
Delete child records from a table DEMO_FIN_INVOICE_LINES first. Then, if child records are successfully deleted, delete the parent record from a table DEMO_FIN_INVOICES.
DELETE FROM demo_fin_invoice_lines
WHERE invoice_id = 1;
DELETE FROM demo_fin_invoices
WHERE invoice_id = 1;
In our scenario a custom error is going to be risen from a trigger when the first delete statement is executed, as expected.
[Error] Execution (90: 13): ORA-20001: Period is locked!
ORA-06512: at "HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK", line 16
ORA-04088: error during execution of trigger 'HR.DEMO_FIN_INVOICE_LIN_TRG_LOCK'
Pros: Simple solution and it requires no changes in triggers, no additional storage objects and has no significant impact on performances.
Cons: 2 separate transactions required.
Read data from a parent table before the tranasaction
This is a classic approach for solving a mutating trigger issue.
Data is read from a mutating table into a temporaty storage like global temporary table, PL/SQL collection, APEX collection... and it is used in the trigger from temporary storage instead.
Let's create a global temporary table for storing data of invoice ID and locked period.
CREATE GLOBAL TEMPORARY TABLE demo_fin_invoices_lock_gtt (
invoice_id number,
lock_period_yn varchar2(1)
) ON COMMIT DELETE ROWS;
INSTEAD OF triggers are brilliant in solving mutating table issues.
Let's populate the global temporary table before the delete statement.
Then let's use this data to check if the period is locked.
CREATE OR REPLACE TRIGGER demo_fin_invoice_lin_trg_lock
FOR INSERT OR UPDATE OR DELETE ON demo_fin_invoice_lines
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
INSERT INTO demo_fin_invoices_lock_gtt (invoice_id, lock_period_yn)
SELECT
i.invoice_id,
dfp.lock_period_yn
FROM
demo_fin_invoices i
JOIN demo_fin_periods dfp ON i.period_id = dfp.period_id
;
END before statement;
BEFORE EACH ROW IS
l_locked_yn varchar2(1);
BEGIN
SELECT lock_period_yn
INTO l_locked_yn
FROM demo_fin_invoices_lock_gtt
WHERE invoice_id = nvl(:new.invoice_id, :old.invoice_id);
if l_locked_yn = 'Y' then
RAISE_APPLICATION_ERROR(-20001, 'Period is locked!');
end if;
END BEFORE EACH ROW;
END;
Pros: Data deletion is executed as a single transaction.
Cons: It requires global temporary table to be created + reading data into a global temporary table slows down data deletion.
