{"id":214,"date":"2008-01-09T22:23:01","date_gmt":"2008-01-09T21:23:01","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=214"},"modified":"2008-01-09T22:23:01","modified_gmt":"2008-01-09T21:23:01","slug":"oracle-11g-compound-trigger","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=214","title":{"rendered":"Oracle 11G – Compound Trigger"},"content":{"rendered":"

In Oracle 11G it is now possible to create a compound trigger. \"mutating_table\"<\/a>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. <\/p>\n

<\/p>\n

Using Oracle 11G you now have \"mutating_table_common_workaround\"<\/a>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.<\/p>\n

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.<\/p>\n

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:<\/p>\n

\n
\n
Inserting 2 rows...<\/pre>\n
 <\/pre>\n
Before statement<\/pre>\n
Before row<\/pre>\n
Inserting<\/pre>\n
:new.value ><\/span>1st value<<\/span><\/pre>\n
After row<\/pre>\n
Inserting<\/pre>\n
:new.value ><\/span>1st value<<\/span><\/pre>\n
After statement<\/pre>\n
 <\/pre>\n
1 row inserted<\/pre>\n
 <\/pre>\n
Before statement<\/pre>\n
Before row<\/pre>\n
Inserting<\/pre>\n
:new.value ><\/span>2nd value<<\/span><\/pre>\n
After row<\/pre>\n
Inserting<\/pre>\n
:new.value ><\/span>2nd value<<\/span><\/pre>\n
After statement<\/pre>\n
 <\/pre>\n
1 row inserted<\/pre>\n
Updating 2 rows...<\/pre>\n
 <\/pre>\n
Before statement<\/pre>\n
Before row<\/pre>\n
Updating<\/pre>\n
:new.value ><<\/span><\/pre>\n
:old.value ><\/span>1st value<<\/span><\/pre>\n
After row<\/pre>\n
Updating<\/pre>\n
:new.value ><\/span>updated values<<\/span><\/pre>\n
:old.value ><\/span>1st value<<\/span><\/pre>\n
Before row<\/pre>\n
Updating<\/pre>\n
:new.value ><<\/span><\/pre>\n
:old.value ><\/span>2nd value<<\/span><\/pre>\n
After row<\/pre>\n
Updating<\/pre>\n
:new.value ><\/span>updated values<<\/span><\/pre>\n
:old.value ><\/span>2nd value<<\/span><\/pre>\n
After statement<\/pre>\n
 <\/pre>\n
2 rows updated<\/pre>\n
 <\/pre>\n
Rollback complete<\/pre>\n<\/p><\/div>\n<\/div>\n
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: <\/p>\n
\n
\n
Inserting 2 rows (using old style triggers)...<\/pre>\n
 <\/pre>\n
Before row<\/pre>\n
:new.value ><\/span>1st value<<\/span><\/pre>\n
After row<\/pre>\n
:new.value ><\/span>1st value<<\/span><\/pre>\n
 <\/pre>\n
1 row inserted<\/pre>\n
 <\/pre>\n
Before row<\/pre>\n
:new.value ><\/span>2nd value<<\/span><\/pre>\n
After row<\/pre>\n
:new.value ><\/span>2nd value<<\/span><\/pre>\n
 <\/pre>\n
1 row inserted<\/pre>\n
Updating 2 rows (using old style triggers)...<\/pre>\n
 <\/pre>\n
Before row<\/pre>\n
:new.value ><\/span>updated values<<\/span><\/pre>\n
:old.value ><\/span>1st value<<\/span><\/pre>\n
After row<\/pre>\n
:new.value ><\/span>updated values<<\/span><\/pre>\n
:old.value ><\/span>1st value<<\/span><\/pre>\n
Before row<\/pre>\n
:new.value ><\/span>updated values<<\/span><\/pre>\n
:old.value ><\/span>2nd value<<\/span><\/pre>\n
After row<\/pre>\n
:new.value ><\/span>updated values<<\/span><\/pre>\n
:old.value ><\/span>2nd value<<\/span><\/pre>\n
 <\/pre>\n
2 rows updated<\/pre>\n
 <\/pre>\n
Rollback complete<\/pre>\n<\/p><\/div>\n<\/div>\n
It seems like the :NEW values are not populated in the compound trigger. I am using the following version of Oracle:<\/p>\n<\/p>\n
\n
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production\nPL\/SQL Release 11.1.0.6.0 - Production\nCORE    11.1.0.6.0    Production\nTNS for 32-bit Windows: Version 11.1.0.6.0 - Production\nNLSRTL Version 11.1.0.6.0 - Production\n<\/span><\/div><\/pre>\n
<\/div>\n
            
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<\/a> to run the test scripts<\/a> 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. <\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-214","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/214","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=214"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/214\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=214"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}