Avoid Mutating Error in a Oracle DB Table Trigger

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;