Oracle 11G – Compound Trigger

In Oracle 11G it is now possible to create a compound trigger. mutating_tableOne 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 mutating_table_common_workaroundthe 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.

Leave a Reply

Your email address will not be published.