Mutating tables

Mutating tables are the ones under a change, literary to say. In PL/SQL you will face mutating tables error in case you are working on a row level trigger.

The problem arises when you are not only dependent on values provided by : new and : old variables but if you are going to user current data inside the table which is under the change. The problem is that in case of multiple row updates there is always a possible case of rollback during the next row updates. It means you have to wait for the whole update (all rows) to finish and then you can use table’s data, so you need a statement level trigger.

But it is not so easy, what if you really need to know about each row? in that case you have to try a trick, namely to catch each changing row’s id, in a row level trigger, and afterwards, if whole update was successful, use those ids in a statement level trigger. Unfortunately you need another trigger to clear the list of ids before each change begins, so you need 3 triggers instead of one single row level trigger.

The address below has a good explanation of codes if you need it to that level:

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936