CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
Darren Christopher
Darren Christopher

Reputation: 4821

Oracle - "table is mutating, trigger/function may not see it" error on update a table with no trigger

I am getting this error: "ORA-04091: table FASHIONRETAILER.Payslips is mutating, trigger/function may not see it" on when trying to update on Payslips table, which has no triggers in it. The screenshot is attached proving there is no trigger on the table. Below is my update statement:

UPDATE "Payslips" 
SET "NETPAY" = calculate_net_pay(user_id)
WHERE "PAYSLIPSID" = (
    SELECT "Payslips"."PAYSLIPSID" FROM "Payslips" 
    WHERE "Payslips"."USERID" = user_id AND "Payslips"."Date" = (
        SELECT MAX("Payslips"."Date") FROM "Payslips" WHERE "Payslips"."USERID" = user_id 
        AND "Payslips"."NETPAY" IS NULL));

The calculate_net_pay(user_id) function and the sub-query in WHERE constraint are working properly.

No triggers on Payslips table

Upvotes: 0

Views: 2942

Answers (3)

0xdb
0xdb

Reputation: 3697

You can try to use pragma autonomous_transaction in the declare section of your function.
I don't see what calculate_net_pay() function exactly do. Therefore may be that is not the best solution for your case but it should work.
More about autonomous transactions see here

Upvotes: 2

Goran Stefanović
Goran Stefanović

Reputation: 328

You cannot query the same table you are updating in a function or a trigger - your function calculate_net_pay does just that, it tries to calculate net pay for a user by querying table Payslips which you are trying to update.

Try using subquery instead of a function.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Does this solve the problem and do what you want?

UPDATE "Payslips"  p
SET "NETPAY" = calculate_net_pay(p.user_id)
WHERE p."Date" = (SELECT MAX(p2."Date")
                  FROM "Payslips" p2
                  WHERE p2."USERID" = p."USERID" AND
                        p2."NETPAY" IS NULL
                 );

Upvotes: 0

Related Questions