Mutating Table Error

http://arunkaushikoracle.blogspot.in/2013/01/what-is-mutation-how-to-avoid-mutation.html
best : https://decipherinfosys.wordpress.com/2009/06/22/mutating-tabletrigger-error-and-how-to-resolve-it/

Mutating error :
The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating.
Mutating error normally occurs when we are performing some DML operations and we are trying to select the affected record from the same trigger. So basically we are trying to select records in the trigger from the table that owns the trigger. This creates inconsistency and Oracle throws a mutating error

there are different ways we can handle mutating table errors
1.First one is to create statement level trigger instead of row level.
2.By defining row level trigger as an autonomous transaction, we got rid of mutating table error but result is not correct.
3.In version 11g, Oracle made it much easier with introduction of compound triggers
       

SQL> CREATE TABLE mtt_same_table
  2  (
  3     col1   NUMBER,
  4     col2   VARCHAR2 (10)
  5  );

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER mutation_on_same_table
  2     BEFORE INSERT OR UPDATE OR DELETE
  3     ON mtt_same_table
  4     FOR EACH ROW
  5  DECLARE
  6     vcount   NUMBER;
  7  BEGIN
  8     SELECT COUNT (*) INTO vcount FROM mtt_same_table;
  9
 10     DBMS_OUTPUT.put_line ('mtt');
 11  END;
 12  /

Trigger created.

SQL>
SQL> SELECT * FROM mtt_same_table;

no rows selected

SQL>
SQL> INSERT INTO mtt_same_table
  2       VALUES (1, 'arun');
mtt

1 row created.

/*When we select from sub query ,either its one value or many mutation error you face
because oracle don’t know how many rows are there until it fetch the rows */

SQL> INSERT INTO mtt_same_table
  2     SELECT sal, ename
  3       FROM emp
  4      WHERE deptno = 10;
INSERT INTO mtt_same_table
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

SQL> insert into mtt_same_table select 1,'arun' from dual;

insert into mtt_same_table select 1,'arun' from dual
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

/* Way (3): In version 11g, Oracle made it much easier with introduction of compound triggers */

CREATE OR REPLACE TRIGGER mutation_on_same_table FOR
   INSERT
   ON mtt_same_table
   COMPOUND TRIGGER                               --added for compound trigger
   v_count   NUMBER;
   BEFORE EACH ROW
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Insert IS done');
   END
   BEFORE EACH ROW;

   BEFORE STATEMENT
   IS
   BEGIN
      SELECT COUNT (*) INTO v_count FROM mtt_same_table;

      DBMS_OUTPUT.put_line ('Total records are ' || SQL%ROWCOUNT);
   END
   BEFORE STATEMENT;

END mutation_on_same_table;
/

Trigger created.

SQL> /* Formatted on 7/14/2016 12:08:58 PM (QP5 v5.256.13226.35510) */
SQL> INSERT INTO mtt_same_table
  2     SELECT sal, ename
  3       FROM emp
  4      WHERE deptno = 10;

3 rows created.