Developers from other databases may be a little surprised when they get this error. This occurs due to how Oracle deals with read consistency. Be aware that the mutating table error is trying to protect you. Be careful with any of these solutions that you are not creating data integrity issues with your solution.
Different ways of dealing with this include:
- Perform the solution using a PL/SQL table or a Global temporary table. A statement level trigger can then perform the modification. Statement level triggers due not have to deal with the mutating table error, only row level triggers. Tom Kyte has a good example of this.
- Autonomous transactions allow a row-level trigger to run a block of code that runs in its own transaction environment. Or to call a PL/SQL procedure that runs in a separate transaction environment that does not cause a mutating table trigger error. Make sure that you have not created any design issues with these solutions. Oracle is protecting you for a reason. The autonomous trigger or PL/SQL block does not understand that it is running as part of a row-level trigger because it has a completely separate transactional context. The autonomous PL/SQL is a good solution when you only need to read the mutating table. The following PL/SQL code is used to set up autonomous blocks of code.
Go to the George Trujillo blog and click on Video podcasts for an example of using autonomous transactions.