In Oracle 11G it is now possible to create a compound trigger. One piece of trigger code that holds all the code for the different triggers you would want. Why do we want to have this? Consider a solution for a mutating table problem. Normally you would use about at least three different triggers and a package with some code to circumvent this kind of issue. A package which holds a data structure to store the relevant data in, which you clean up using the before statement trigger. Then the before or after row trigger which stores the data for the table in the package variable. And then the after statement trigger which performs the necessary checks.
Using Oracle 11G you now have the possibility to create a compound trigger, which is sort of like a package (without the specification and body implementation) where you have all the possibilities of a package, with semi-persistent data and local procedures and functions and all the types of triggers are combined here. In this compound trigger you can create the AA, which is filled up by the before (or after) row trigger and the after statement trigger performs the necessary checks.
You can create all kinds of methods in this compound trigger, just like you would do in a package, but you cannot reach the procedures and functions defined in the compound trigger from the outside, that means that all the methods a local to the trigger.
It seems (at least on my installation) that the :new values are not filled up correctly in the compound trigger. When I look at the output of my test script, it looks like this:
Especially the update makes me wonder. The new value for the before update triggers seems not to be populated. When I use the old style triggers the output looks like this:
It seems like the :NEW values are not populated in the compound trigger. I am using the following version of Oracle:
Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - Production PL/SQL Release 220.127.116.11.0 - Production CORE 18.104.22.168.0 Production TNS for 32-bit Windows: Version 22.214.171.124.0 - Production NLSRTL Version 126.96.36.199.0 - Production
I think it’s an issue with this installation of Oracle in VMWare, because I couldn’t replay this issue with a new installation of Oracle on my computer. I also asked Steven to run the test scripts on 11G, and his results were also the way I expected them to be. It does make me wonder what’s wrong with the installation on VMWare, so if anyone knows what I should check, please let me know.