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