{"id":201,"date":"2007-11-03T18:37:40","date_gmt":"2007-11-03T17:37:40","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=201"},"modified":"2007-11-03T18:37:40","modified_gmt":"2007-11-03T17:37:40","slug":"oracle-11g-follows-in-trigger","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=201","title":{"rendered":"Oracle 11G – Follows in Trigger"},"content":{"rendered":"

I’ve seen a presentation on the 11G new features from Alex and Lucas at Amis<\/a>. One of the questions posed there was when you are using the new follows<\/strong> option in triggers, what would happen if you have two triggers following the same one. A bit like the way it”s done right now.<\/p>\n

<\/p>\n

Suppose you have multiple trigger on the same event on the same table, for instance when you have a packaged application where you are not allowed to change any of the code. But you want to add your own code. Or you have some standard triggers that you generate using for example CodeGen<\/a>. If you add a trigger to an event that already has a trigger on it, there is no way of knowing when this is executed, before or after the existing trigger. If you rely on values that may be changed in the first trigger there was no way to be sure this trigger had been fired before your code is executed.<\/p>\n

In Oracle 11G you now have the possibility to add the follows<\/strong> option to a trigger, to make sure the code is executed after the original trigger. (from the documentation at Oracle<\/a>)<\/p>\n<\/p>\n

\n
CREATE [ OR REPLACE ] TRIGGER [schema.] trigger\n  { simple_dml_trigger\n  <\/span>|<\/span> compound_dml_trigger\n  <\/span>|<\/span> non_dml_trigger\n  }\n  [ FOLLOWS [schema.] trigger [[schema.] trigger]...]\n  [ ENABLE <\/span>|<\/span> DISABLE ]\n  [ WHEN (condition) ]\n  trigger_body<\/span><\/div><\/pre>\n
<\/div>\n
When you add the FOLLOWS<\/strong> clause to your trigger, you can make sure it gets fired after the other trigger. But what happens if you have two (or more) triggers that follow a single trigger? One table with three triggers in place. Trigger 2 follows trigger 1 and trigger 3 follows trigger 1 too. Which one gets fired first? Is there any way of knowing? Someone once said that the on with the lowest physical number will be fired first? But how is this calculated? <\/p>\n
I have created a script to try these issues:<\/p>\n<\/p>\n
\n
SET<\/span> FEEDBACK OFF\nprompt <\/span>Create<\/span> a <\/span>table<\/span>\n<\/span>CREATE<\/span> <\/span>table<\/span> T_TRIGGERTEST\n(\n  ID    <\/span>NUMBER<\/span> <\/span>not<\/span> <\/span>null<\/span>,\n  <\/span>VALUE<\/span> <\/span>VARCHAR2<\/span>(<\/span>200<\/span>)\n)\ntablespace USERS;\n<\/span>--<\/span> Create\/Recreate primary, unique and foreign key constraints <\/span>\n<\/span>alter<\/span> <\/span>table<\/span> T_TRIGGERTEST\n  add <\/span>constraint<\/span> PK_ID primary key (ID);\nprompt <\/span>create<\/span> a sequence...\n<\/span>CREATE<\/span> SEQUENCE t_triggertest_seq <\/span>START<\/span> <\/span>WITH<\/span> <\/span>1<\/span> NOCACHE;\nprompt <\/span>create<\/span> <\/span>trigger<\/span> <\/span>1<\/span>...\n<\/span>create<\/span> <\/span>or<\/span> <\/span>replace<\/span> <\/span>trigger<\/span> tr_triggertest_<\/span>1<\/span>\n  <\/span>before<\/span> <\/span>insert<\/span> <\/span>on<\/span> t_triggertest  \n  <\/span>for<\/span> <\/span>each<\/span> <\/span>row<\/span>\n<\/span>BEGIN<\/span>\n  :new.id :<\/span>=<\/span> t_triggertest_seq.nextval;\n  :new.value :<\/span>=<\/span> :new.value || <\/span>'<\/span> tr_triggertest_1<\/span>'<\/span>;\n<\/span>end<\/span> tr_triggertest_<\/span>1<\/span>;\n<\/span>\/<\/span>\nprompt <\/span>create<\/span> <\/span>trigger<\/span> <\/span>2<\/span>...\n<\/span>create<\/span> <\/span>or<\/span> <\/span>replace<\/span> <\/span>trigger<\/span> tr_triggertest_<\/span>2<\/span>\n  <\/span>before<\/span> <\/span>insert<\/span> <\/span>on<\/span> t_triggertest  \n  <\/span>for<\/span> <\/span>each<\/span> <\/span>ROW<\/span>\n  follows tr_triggertest_<\/span>1<\/span>\n<\/span>begin<\/span>\n  :new.value :<\/span>=<\/span> :new.value || <\/span>'<\/span> tr_triggertest_2<\/span>'<\/span>;\n<\/span>end<\/span> tr_triggertest_<\/span>2<\/span>;\n<\/span>\/<\/span>\nprompt <\/span>create<\/span> <\/span>trigger<\/span> <\/span>3<\/span>...\n<\/span>create<\/span> <\/span>or<\/span> <\/span>replace<\/span> <\/span>trigger<\/span> tr_triggertest_<\/span>3<\/span>\n  <\/span>before<\/span> <\/span>insert<\/span> <\/span>on<\/span> t_triggertest  \n  <\/span>for<\/span> <\/span>each<\/span> <\/span>ROW<\/span>\n  follows tr_triggertest_<\/span>1<\/span>\n<\/span>begin<\/span>\n  :new.value :<\/span>=<\/span> :new.value || <\/span>'<\/span> tr_triggertest_3<\/span>'<\/span>;\n<\/span>end<\/span> tr_triggertest_<\/span>3<\/span>;\n<\/span>\/<\/span>\nprompt <\/span>insert<\/span> a <\/span>record<\/span> <\/span>into<\/span> <\/span>the<\/span> <\/span>table<\/span>...\n<\/span>INSERT<\/span> <\/span>INTO<\/span> t_triggertest (<\/span>value<\/span>) <\/span>VALUES<\/span> (<\/span>'<\/span>value<\/span>'<\/span>);\nprompt query <\/span>the<\/span> <\/span>table<\/span> <\/span>to<\/span> see <\/span>the<\/span> <\/span>values<\/span>\n<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> t_triggertest;\nprompt <\/span>create<\/span> <\/span>trigger<\/span> <\/span>2<\/span> again...\n<\/span>create<\/span> <\/span>or<\/span> <\/span>replace<\/span> <\/span>trigger<\/span> tr_triggertest_<\/span>2<\/span>\n  <\/span>before<\/span> <\/span>insert<\/span> <\/span>on<\/span> t_triggertest  \n  <\/span>for<\/span> <\/span>each<\/span> <\/span>ROW<\/span>\n  follows tr_triggertest_<\/span>1<\/span>\n<\/span>begin<\/span>\n  :new.value :<\/span>=<\/span> :new.value || <\/span>'<\/span> tr_triggertest_2<\/span>'<\/span>;\n<\/span>end<\/span> tr_triggertest_<\/span>2<\/span>;\n<\/span>\/<\/span>\nprompt <\/span>insert<\/span> another <\/span>record<\/span>...\n<\/span>INSERT<\/span> <\/span>INTO<\/span> t_triggertest (<\/span>value<\/span>) <\/span>VALUES<\/span> (<\/span>'<\/span>value<\/span>'<\/span>);\nprompt query <\/span>the<\/span> <\/span>table<\/span> <\/span>to<\/span> see <\/span>the<\/span> <\/span>values<\/span>...\n<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> t_triggertest;\nprompt drop <\/span>trigger<\/span> <\/span>3<\/span>...\nDROP <\/span>TRIGGER<\/span> tr_triggertest_<\/span>3<\/span>;\nprompt <\/span>insert<\/span> another <\/span>record<\/span>...\n<\/span>INSERT<\/span> <\/span>INTO<\/span> t_triggertest (<\/span>value<\/span>) <\/span>VALUES<\/span> (<\/span>'<\/span>value<\/span>'<\/span>);\nprompt query <\/span>the<\/span> <\/span>table<\/span> <\/span>to<\/span> see <\/span>the<\/span> <\/span>values<\/span>...\n<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> t_triggertest;\nprompt <\/span>create<\/span> <\/span>trigger<\/span> <\/span>3<\/span> again...\n<\/span>create<\/span> <\/span>or<\/span> <\/span>replace<\/span> <\/span>trigger<\/span> tr_triggertest_<\/span>3<\/span>\n  <\/span>before<\/span> <\/span>insert<\/span> <\/span>on<\/span> t_triggertest  \n  <\/span>for<\/span> <\/span>each<\/span> <\/span>ROW<\/span>\n  follows tr_triggertest_<\/span>1<\/span>\n<\/span>begin<\/span>\n  :new.value :<\/span>=<\/span> :new.value || <\/span>'<\/span> tr_triggertest_3<\/span>'<\/span>;\n<\/span>end<\/span> tr_triggertest_<\/span>3<\/span>;\n<\/span>\/<\/span>\nprompt <\/span>insert<\/span> another <\/span>record<\/span>...\n<\/span>INSERT<\/span> <\/span>INTO<\/span> t_triggertest (<\/span>value<\/span>) <\/span>VALUES<\/span> (<\/span>'<\/span>value<\/span>'<\/span>);\nprompt query <\/span>the<\/span> <\/span>table<\/span> <\/span>to<\/span> see <\/span>the<\/span> <\/span>values<\/span>\n<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> t_triggertest;\nprompt <\/span>create<\/span> <\/span>trigger<\/span> <\/span>3<\/span> again, but this <\/span>time<\/span> it follows <\/span>trigger<\/span> <\/span>2<\/span>\n<\/span>create<\/span> <\/span>or<\/span> <\/span>replace<\/span> <\/span>trigger<\/span> tr_triggertest_<\/span>3<\/span>\n  <\/span>before<\/span> <\/span>insert<\/span> <\/span>on<\/span> t_triggertest  \n  <\/span>for<\/span> <\/span>each<\/span> <\/span>ROW<\/span>\n  follows tr_triggertest_<\/span>2<\/span>\n<\/span>begin<\/span>\n  :new.value :<\/span>=<\/span> :new.value || <\/span>'<\/span> tr_triggertest_3<\/span>'<\/span>;\n<\/span>end<\/span> tr_triggertest_<\/span>3<\/span>;\n<\/span>\/<\/span>\nprompt <\/span>insert<\/span> another <\/span>record<\/span>...\n<\/span>INSERT<\/span> <\/span>INTO<\/span> t_triggertest (<\/span>value<\/span>) <\/span>VALUES<\/span> (<\/span>'<\/span>value<\/span>'<\/span>);\nprompt query <\/span>the<\/span> <\/span>table<\/span> <\/span>to<\/span> see <\/span>the<\/span> <\/span>values<\/span>...\n<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> t_triggertest;\nprompt clean up, drop <\/span>the<\/span> <\/span>table<\/span> <\/span>and<\/span> <\/span>the<\/span> sequence...\nDROP <\/span>TABLE<\/span> t_triggertest;\nDROP SEQUENCE t_triggertest_seq;\n\noutput (partially)\nID <\/span>VALUE<\/span>\n<\/span>--<\/span> <\/span>--------------------------------------------------------------------------------\n<\/span> <\/span>1<\/span> <\/span>value<\/span> tr_triggertest_<\/span>1<\/span> tr_triggertest_<\/span>3<\/span> tr_triggertest_<\/span>2<\/span>\n <\/span>2<\/span> <\/span>value<\/span> tr_triggertest_<\/span>1<\/span> tr_triggertest_<\/span>3<\/span> tr_triggertest_<\/span>2<\/span>\n <\/span>3<\/span> <\/span>value<\/span> tr_triggertest_<\/span>1<\/span> tr_triggertest_<\/span>2<\/span>\n <\/span>4<\/span> <\/span>value<\/span> tr_triggertest_<\/span>1<\/span> tr_triggertest_<\/span>3<\/span> tr_triggertest_<\/span>2<\/span>\n <\/span>5<\/span> <\/span>value<\/span> tr_triggertest_<\/span>1<\/span> tr_triggertest_<\/span>2<\/span> tr_triggertest_<\/span>3<\/span><\/div><\/pre>\n
<\/div>\n
When I check the output it appears that trigger 3 gets fired before trigger 2 no matter when it’s created except when I tell it to fire following trigger 2. I am guessing you can get some other results on your installation of Oracle 11G, but I think this shows that you cannot rely on the order of triggers being fired, except when you tell the triggers to follow eachother,<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ve seen a presentation on the 11G new features from Alex and Lucas at Amis. One of the questions posed there was when you are using the new follows option in triggers, what would happen if you have two triggers following the same one. A bit like the way it”s done right now.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-201","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/201","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=201"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/201\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}