You would have seen this error many a times while writing trigger on a table and trying to perform DQL or DML operation on the same table the trigger is written on.
Error :- ORA-04091: table name is mutating, trigger/function may not see it.
You have multiple solutions to resolve it.
1. Write your code in Before Insert/Update Trigger :- If your piece of code is independent and not using any values from the after insert/Update trigger then You can go ahead with this approach. Just move your piece of code from After Insert/Update trigger to Before Insert/Update. This will never give you mutating trigger error.
2. Use pragma AUTONOMOUS for your piece of code :-
I.e.
I am writing a code in a trigger on ra_customer_Trx_all
Code with Error :-
I am giving only pseudo code :-
After Insert Trigger definition on ra_customer_trx_all
With Error :-
SELECT
cust.ATTRIBUTE5
FROM ra_customer_Trx_all rct, hz_cust_accounts cust
WHERE rct.bill_to_customer_id = cust_account_id
AND rct.customer_Trx_id = :p_customer_Trx_id;
Avoid Error :-
Write the below function somewhere and call it in your logic.
FUNCTION get_cust_acct_id (p_customer_Trx_id IN NUMBER)
RETURN NUMBER IS
p_trx_id NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT
bill_to_customer_id
FROM ra_customer_Trx_all
WHERE rct.customer_Trx_id = :p_customer_Trx_id;
RETURN bill_to_customer_id
END;
