MUTATING

Mutating problem in Triggers:

In a row level trigger, if the triggering event is update or delete and if a select is fired on the base table, then while the operation (update or delete or insert (after insert alone)) is performed, then the trigger fires with an error 'ORA-04091: table ... is mutating'. However, this problem does not arise in the statement level triggers and for before insert (row level) too.

Below are two ways to overcome this issue. 

Use Instead Of Triggers

 Instead Of triggers are the triggers written on views. These are fired instead of the DML operations (Insert or Update or Delete) performed. The way these can be used to overcome the mutation problem is by creating a view for the table that requires a trigger in which the base table has to be queried, and writing an instead of trigger on the view where the base table can be selected and not he view. Expose the view to the user. Thus we can overcome the mutating error. For instead of triggers refer INSTEAD OF TRIGGERS.

 Write Autonomous Triggers

When writing a trigger where the base table is queried, declare the trigger as an autonomous transaction, which is called an autonomous trigger. Thus we can overcome the mutating problem by writing autonomous triggers. For info on autonomous transaction refer AUTONOMOUS TRANSACTION.