Résoudre le problème de la table mutante
Je viens de découvrir l’article "The Trouble with Triggers". Bref, j’avais l’impression de maitriser le sujet mais j’étais surpris par le paragraphe suivant.
Citation:
The developers might find another way around this mutating table constraint. A common technique is to use a package and three triggers (as documented at asktom.oracle.com/tkyte/Mutate. Note that in Oracle Database 11g, there is a new feature, the compound trigger, that can be used in place of this “package and three triggers” technique, but the results will be the same as described below.)
With this technique, the package would have a global variable of a PLSQL table type, such as an array. The global variable would be set to “empty” by a BEFORE statement trigger. Then the global variable would be populated with primary keys by the FOR EACH ROW trigger (the keys of the modified rows) or their rowids. Last, an AFTER statement trigger would iterate over the global variable values and would be able to query the table the trigger was defined on, because the modification has already taken place. This sounds like it would work, and it would, if you were the only user of the database and never had more than one transaction at a time! This trigger solution works purely in a single-user environment, as do many other triggers I’ve reviewed. They work well in isolation but fail to do their job when multiple users invoke them simultaneously.
C’est à lire pour tous ce qui utilise beaucoup les triggers.
