The mutating error conflicts with maintaining a consistent view, therefore, care should be taken to write proper code and avoid such triggers.
The basic reason for this error is the way Oracle manages a read consistent view of data.
The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. Mutation will not occur if a single record is inserted in the table (using VALUES clause).
If bulk insertion is done or data is inserted from another table mutation will occur.
The mutating error is not only encountered during queries, but also for insert, updates and deletes present in the trigger.
There is one odd case where mutation may occur when some other table in the trigger is referred to; below is an example of such a condition. AM10_DTL is the detail table that is related to the master table with the on-delete-cascade option.
AM10_BEF_TRG is created on the master table that queries the detail table for some information.
AM27_TRG' In the above example, as table AM27 is being queried in the trigger AM27_TRG that is based on the same table, a mutating error is received.
It is also possible for ORA-4091 to be encountered when querying a table other than the table on which the trigger is based!
A statement-level trigger can then be used to pickup the stored information and apply it to the table.
A database trigger is a stored procedure that automatically executes whenever an event occurs. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has been occurred and an ‘AFTER UPDATE’ trigger after an update event has been occurred.
AM10_BEF_TRG", line 4 ORA-04088: error during execution of trigger 'SYSTEM.