ORA-04091
Sounds familiar? A friend encountered it today.
Mutating table problem from within a row level trigger on running a SELECT on the same table.
Did some digging around and figured out 2 ways of handling it :-
- Use PRAGMA AUTONOMOUS_TRANSACTION
- Break the trigger into 2 parts – a row level one and an “after operation” statement level trigger and use a PL/SQL table construct to pass data between the two
No point elaborating on it here. Most Oracle Press texts recommend the same solution.
But wait! PRAGMA AUTONOMOUS_TRANSACTION means starting a fresh transaction. Do we really want to do that?
And the second approach essentially is a global variable approach. What about multi-user situations?
And since I have been in non-Oracle territory for the last 5 years or so; it is still taking some time for this to register 
And then; my friend comes back with this from AskTom and things start making a lot of sense. I wont expound on it here and spare you – my non-existent reader – the pain of listening to yet another plagiarized version. In any case, the conclusions are there in the article for you to read and ponder.
As for me; I am left with a sense of intense frustration that titles from Oracle Press such as Oracle 10G PL/SQL Programming talk about the above listed approaches for handling the mutating table problem *without* making even a lame effort to list down the things to guard against as well as the side effects putting these solutions in; introduces into the system.
Thank you Oracle Press! It was nice to have read yet another title from your esteemed stables which leaves the reader with half-baked and potentially destructive information.
Maybe not having your support people write books will help?
