Oracle DB. Insert Trigger with Merge statament inside. Table is mutating

Question

I have two back-end systems (the old one and the new one) that shares an Oracle DB.

In the older system, to save customers data, there are two tables

customers_A

ID   NAME  ETC
1    PETE  ....

customers_B

ID NAME ETC
1  JOSH ...
2  ROSS ...

In the new system I've created a new table called All_Costumer, to join those tables. This new table contains customer ID's of type A and B respectively.

All_Customers

ID           ID_CUSTOMER_A    ID_CUSTOMER_B
A19E----D2B0     1                 null
A19E----D2B1    null                 1
A19E----D2B2    null                 2

So, when the new system creates a new customer of type A, data are inserted on customer_A and All_Customers tables, with customer of type B as well.

Currently, the old system is working too, and when a new customer of type A is created, data is inserted only on customer_A table, but I need that data in All_Customers too.

To solve this, I've created a TRIGGER with a MERGE INTO statement inside, to insert a row in All_Customers if doesn't exist on this table (when a new customer of type A are created by the older system)

CREATE OR REPLACE TRIGGER customers_trg

AFTER INSERT
ON customer_A

FOR EACH ROW

DECLARE
variables that doesn't matters

BEGIN
    MERGE INTO all_customers
        USING (SELECT :new.id id FROM customer_A where id = :new.id) customer
        ON (all_customers.id_customer_a = customer.id)
    WHEN NOT MATCHED THEN
        INSERT (id, id_customer_a)
        VALUES (SYS_GUID(), :new.id, null);
    COMMIT;
END;

But when I try to create a new customer from the older system, I get this error:

ORA-04091: table **customer_A** is mutating, trigger/function may not see it

Any idea to solve this? I've tried adding PRAGMA AUTONOMOUS_TRANSACTION; on DECLARE section, but didn't work.

Note: I can't modify the old system

Alex Poole Alex Poole · Accepted Answer · 2020-03-31T16:51:01

The immediate issue is that you're querying table_a in a trigger against that table; but you don't need to. Your merge query

SELECT :new.id id FROM customer_A where id = :new.id

can simply do

SELECT :new.id id FROM dual

i.e. the clause becomes:

...
        USING (SELECT :new.id id FROM dual) customer
        ON (all_customers.id_customer_a = customer.id)
...

You also can't commit in a trigger - unless it's autonomous, which this shouldn't be. You said you'd tried that, but it breaks if the insert is rolled back, since the merged row will still exist. So hopefully that commit is just a hang-over from trying and rejecting that approach.

But it works in this db<>fiddle, anyway.

If you weren't adding the GUID you could get the same effect with a view:

create or replace view all_customers (id_customer_a, id_customer_b) as
select id, null from customers_a
union all
select null, id from customers_b;

db<>fiddle

Oracle DB. Insert Trigger with Merge statament inside. Table is mutating

1 Answers