What is mutating error ..how to avoid it.

Mutating Table:

A Mutating table is a table that is currently being modified by an UPDATE, DELETE OR INSERT statement,or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.

A table is not considered mutating for statement triggers.

A mutating table cannot be changed because the resulting DML could change data that is in consistent state.

A mutating table is table that is currently being modified by a DML statement and the trigger event also DML statement.
A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change.

A constraining table is a table that might need to be read from for a referential integrity constraint.

Mutating error:

Mutating error occurs in the following scenario:

when we are updating a table (trigger written on a table for update) and at the same time trying to retrieve data from that table.

it will result into mutating table and it will result into mutating error.

This happens with triggers.

It occurs because the trigger is trying to update a row it is currently using.

The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

Ex:

CREATE OR REPLACE TRIGGER mutating_trigger
   BEFORE DELETE
   ON student
   FOR EACH ROW

DECLARE
   ct   NUMBER;

BEGIN
   SELECT COUNT (*)
     INTO ct
     FROM student
    WHERE NO = :OLD.NO;

END mutating_trigger; 

SQL> delete student where no = 1;
                     delete student where no = 1
                        *
                     ERROR at line 1:
                    ORA-04091: table SCOTT.STUDENT is mutating, trigger/function may not see it
                     ORA-06512: at "SCOTT.T", line 4
                     ORA-04088: error during execution of trigger 'SCOTT.T'

HOW TO AVOID MUTATING TABLE ERROR?

By using autonomous transaction
By using statement level trigger