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

Let me explain how Mutating error happens with  an example

Let us take a business scenarios. Assume that there is a table with ID column , Transaction Date , Transaction Type and Transaction Value.

The business need is, if any record inserted into the table with Transaction Type as "Invoice" then it should automatically insert another record with Transaction Type as "Tax" with Transaction value as 10% of the Invoice Transaction Value.

Below is the script initially built for the requirement

DROP TABLE mytransactions;

CREATE TABLE mytransactions
  (
    id     NUMBER,
    tdate  DATE ,
    ttype  VARCHAR2(20) ,
    tvalue NUMBER
  );

DROP TRIGGER mytransactions_ai;

CREATE OR REPLACE TRIGGER mytransactions_ai AFTER
  INSERT ON mytransactions FOR EACH row
BEGIN
    IF (:new.ttype='Invoice') THEN
      INSERT INTO mytransactions VALUES
        (:new.id,sysdate,'Tax',:new.tvalue*0.1 );
      dbms_output.put_line('Tax Inserted');
    END IF;
END;
/

INSERT INTO mytransactions VALUES (1,sysdate,'Invoice',100);

When we try to execute the above insert script, we might end up with the below error.

ORA-04091: table MYTRANSACTIONS is mutating, trigger/function may not see it
ORA-06512: at "MYTRANSACTIONS_AI", line 3
ORA-04088: error during execution of trigger 'MYTRANSACTIONS_AI'

To overcome the mutating error, we should use AUTONOMOUS_TRANSACTION in the trigger.

CREATE OR REPLACE TRIGGER mytransactions_ai AFTER
  INSERT ON mytransactions FOR EACH row
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    IF (:new.ttype='Invoice') THEN
      INSERT INTO mytransactions VALUES
        (:new.id,sysdate,'Tax',:new.tvalue*0.1 );
      dbms_output.put_line('Tax Inserted');
      COMMIT;
    END IF;
END;
/

After modifying the trigger we should able to insert the records successfully.


        ID TDATE     TTYPE                    TVALUE
---------- --------- -------------------- ----------
         1 11-JAN-17 Invoice                     100
         1 11-JAN-17 Tax                          10

Incompatibility Setting for a Concurrent Program

- Incompatibility settings are used to prevent the program running parallel
- This can be set at Global or at Domain Level
- When we set it at Global, the program cannot be run parallel at any point of time. It is taken care by the Conflict Manager.
- To set the incompatibility at Domain level, we should understand about conflict domain.

Conflict Domain is nothing but a logic partition of the data available in the database. The data can be partitioned at many level. For example, SOB, OU or INV level.

For example, if the Program needs to be run parallel across the operating unit but sequential within the operating unit then we need to define the Conflict Domain

Steps to define Conflict Domain at Operating Unit level.
1. Create a concurrent program
2. Add parameters
3. Select one parameter as Conflict Domain in the setup (Available in the parameter window itself). The parameter should be defaulted from profile "MO: Operating Unit"
4. Set the incompatibility for the program for itself at Domain level

 By following the above setup, we can achieve the requirement.