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:

Inserting 2 rows...

Before statement

Before row

Inserting

:new.value >1st value<

After row

Inserting

:new.value >1st value<

After statement

1 row inserted

Before statement

Before row

Inserting

:new.value >2nd value<

After row

Inserting

:new.value >2nd value<

After statement

1 row inserted

Updating 2 rows...

Before statement

Before row

Updating

:new.value ><

:old.value >1st value<

After row

Updating

:new.value >updated values<

:old.value >1st value<

Before row

Updating

:new.value ><

:old.value >2nd value<

After row

Updating

:new.value >updated values<

:old.value >2nd value<

After statement

2 rows updated

Rollback complete

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:

Inserting 2 rows (using old style triggers)...

Before row

:new.value >1st value<

After row

:new.value >1st value<

1 row inserted

Before row

:new.value >2nd value<

After row

:new.value >2nd value<

1 row inserted

Updating 2 rows (using old style triggers)...

Before row

:new.value >updated values<

:old.value >1st value<

After row

:new.value >updated values<

:old.value >1st value<

Before row

:new.value >updated values<

:old.value >2nd value<

After row

:new.value >updated values<

:old.value >2nd value<

2 rows updated

Rollback complete

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 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0    Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.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.