When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1, to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot ...
For the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?