{"id":239,"date":"2008-05-01T08:07:30","date_gmt":"2008-05-01T07:07:30","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=239"},"modified":"2008-05-01T08:07:30","modified_gmt":"2008-05-01T07:07:30","slug":"triggers","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=239","title":{"rendered":"Triggers…"},"content":{"rendered":"

I ran into an issue at a customer site where certain triggers were disabled in the database where they should be enabled. It appeared that an update script, that is run every night, first disables all triggers on a couple of tables. Then does what it needs to do, without the overhead of the trigger code. and then, when it’s done, it enables all triggers again.<\/p>\n

If the update code fails for whatever reason, then the re-enabling of the triggers is not performed, leaving the triggers in disabled state on the database, which can cause problems in everyday use. What you would actually want is a situation where you can disable the triggers, but just for the current session. Any other session should have the triggers enabled at all times. Unfortunately Oracle<\/a> doesn’t support this kind of enabling\/disabling of triggers. We do however have access to all the possibilities of PL\/SQL in triggers so we can build a solution to this problem ourselves.<\/p>\n

<\/p>\n

I have come up with a semaphore package which allows me to set and clear a flag or semaphore which I can then check in the trigger code and then, depending on the value of this semaphore execute or skip the code in the trigger. I have also used the knowledge I posted here<\/a> about a boolean that’s really an integer.<\/p>\n

The package<\/p>\n

\n
CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>PACKAGE<\/span> bar_semaphore\n<\/span>IS<\/span>\n  <\/span>--<\/span> Author  : Patrick Barel<\/span>\n<\/span>\n  <\/span>--<\/span> Public function and procedure declarations<\/span>\n<\/span>  <\/span>FUNCTION<\/span> sem_emp\n    <\/span>RETURN<\/span> <\/span>BOOLEAN<\/span>;\n\n  <\/span>PROCEDURE<\/span> set_sem_emp;\n\n  <\/span>PROCEDURE<\/span> clr_sem_emp;\n<\/span>END<\/span> bar_semaphore;\n<\/span><\/div><\/pre>\n
<\/div>\n
<\/p>\n
\n
CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>BODY<\/span> bar_semaphore\n<\/span>IS<\/span>\n  <\/span>--<\/span> Private variable declarations<\/span>\n<\/span>  g_emp                                    <\/span>PLS_INTEGER<\/span>;\n\n  <\/span>--<\/span> Function and procedure implementations<\/span>\n<\/span>  <\/span>PROCEDURE<\/span> MINVALUE(\n    p_variable_in_out              <\/span>IN<\/span> <\/span>OUT<\/span>  <\/span>PLS_INTEGER<\/span>\n  , p_min_value_in                 <\/span>IN<\/span>      <\/span>PLS_INTEGER<\/span> <\/span>DEFAULT<\/span> <\/span>0<\/span> )\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    <\/span>IF<\/span> p_variable_in_out <\/span><<\/span> p_min_value_in <\/span>THEN<\/span>\n      p_variable_in_out   :<\/span>=<\/span> p_min_value_in;\n    <\/span>END<\/span> <\/span>IF<\/span>;\n  <\/span>END<\/span> MINVALUE;\n\n  <\/span>PROCEDURE<\/span> initialization\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    g_emp   :<\/span>=<\/span> <\/span>0<\/span>;\n  <\/span>END<\/span> initialization;\n\n  <\/span>FUNCTION<\/span> sem_emp\n    <\/span>RETURN<\/span> <\/span>BOOLEAN<\/span>\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    <\/span>RETURN<\/span>( g_emp <\/span>><\/span> <\/span>0<\/span> );\n  <\/span>END<\/span> sem_emp;\n\n  <\/span>PROCEDURE<\/span> set_sem_emp\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    g_emp   :<\/span>=<\/span> g_emp <\/span>+<\/span> <\/span>1<\/span>;\n  <\/span>END<\/span> set_sem_emp;\n\n  <\/span>PROCEDURE<\/span> clr_sem_emp\n  <\/span>IS<\/span>\n  <\/span>BEGIN<\/span>\n    g_emp   :<\/span>=<\/span> g_emp <\/span>-<\/span> <\/span>1<\/span>;\n    MINVALUE( g_emp, <\/span>0<\/span> );\n  <\/span>END<\/span> clr_sem_emp;\n<\/span>BEGIN<\/span>\n  initialization;\n<\/span>END<\/span> bar_semaphore;\n<\/span><\/div><\/pre>\n
<\/div>\n
The triggers<\/p>\n
\n
CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>TRIGGER<\/span> tr_emp_bri\n  <\/span>BEFORE<\/span> <\/span>INSERT<\/span>\n  <\/span>ON<\/span> emp\n  <\/span>FOR<\/span> <\/span>EACH<\/span> <\/span>ROW<\/span>\n<\/span>DECLARE<\/span>\n<\/span>--<\/span> local variables here<\/span>\n<\/span>BEGIN<\/span>\n  <\/span>IF<\/span> <\/span>NOT<\/span>( bar_semaphore.sem_emp ) <\/span>THEN<\/span>\n    <\/span>IF<\/span> :NEW.ename <\/span>IS<\/span> <\/span>NULL<\/span> <\/span>THEN<\/span>\n      :NEW.ename   :<\/span>=<\/span> <\/span>'<\/span><EMPTY><\/span>'<\/span>;\n    <\/span>END<\/span> <\/span>IF<\/span>;\n  <\/span>END<\/span> <\/span>IF<\/span>;\n<\/span>END<\/span> tr_emp_bri;<\/span><\/div><\/pre>\n
<\/div>\n
<\/p>\n
\n
CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>TRIGGER<\/span> tr_emp_bru\n  <\/span>BEFORE<\/span> <\/span>UPDATE<\/span>\n  <\/span>ON<\/span> emp\n  <\/span>FOR<\/span> <\/span>EACH<\/span> <\/span>ROW<\/span>\n<\/span>DECLARE<\/span>\n<\/span>--<\/span> local variables here<\/span>\n<\/span>BEGIN<\/span>\n  <\/span>IF<\/span> <\/span>NOT<\/span>( bar_semaphore.sem_emp ) <\/span>THEN<\/span>\n    <\/span>IF<\/span> :NEW.ename <\/span>IS<\/span> <\/span>NULL<\/span> <\/span>THEN<\/span>\n      :NEW.ename   :<\/span>=<\/span> <\/span>'<\/span><EMPTY><\/span>'<\/span>;\n    <\/span>END<\/span> <\/span>IF<\/span>;\n  <\/span>END<\/span> <\/span>IF<\/span>;\n<\/span>END<\/span> tr_emp_bru;<\/span><\/div><\/pre>\n
<\/div>\n
The script<\/p>\n
\n
clear screen\n<\/span>set<\/span> serveroutput <\/span>on<\/span>\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>update<\/span> emp <\/span>set<\/span> ename <\/span>=<\/span> <\/span>null<\/span> <\/span>where<\/span> empno <\/span>=<\/span> <\/span>7934<\/span>;\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>rollback<\/span>;\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>exec<\/span> bar_semaphore.set_sem_emp;\n<\/span>update<\/span> emp <\/span>set<\/span> ename <\/span>=<\/span> <\/span>null<\/span> <\/span>where<\/span> empno <\/span>=<\/span> <\/span>7934<\/span>;\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>rollback<\/span>;\n<\/span>exec<\/span> bar_semaphore.clr_sem_emp;\n<\/span>update<\/span> emp <\/span>set<\/span> ename <\/span>=<\/span> <\/span>null<\/span> <\/span>where<\/span> empno <\/span>=<\/span> <\/span>7934<\/span>;\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>rollback<\/span>;\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>exec<\/span> bar_semaphore.set_sem_emp;\n<\/span>update<\/span> emp <\/span>set<\/span> ename <\/span>=<\/span> <\/span>null<\/span> <\/span>where<\/span> empno <\/span>=<\/span> <\/span>7934<\/span>;\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>rollback<\/span>;\nconnect scott<\/span>\/<\/span>tiger\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;\n<\/span>update<\/span> emp <\/span>set<\/span> ename <\/span>=<\/span> <\/span>null<\/span> <\/span>where<\/span> empno <\/span>=<\/span> <\/span>7934<\/span>;\n<\/span>select<\/span> <\/span>*<\/span> <\/span>from<\/span> emp;<\/span><\/div><\/pre>\n
<\/div>\n
The output of the script shows that when a new session is connected and the semaphore is set in the other session, disabling the triggers, this has no influence on the currently connected session. It is as if the semaphore is never set and the triggers function as normal. This way the triggers can be turned off for certain scripts, or pieces of code, that don’t want to be bothered by the code in the trigger, while other session still have this code in place and still have it turned on.<\/p>\n
You can either choose to have a single semaphore in a package, or a single semaphore for every table or even a semaphores for different functions in the code. This way execution can be turned off and on exactly the way you want it.<\/p>\n","protected":false},"excerpt":{"rendered":"
I ran into an issue at a customer site where certain triggers were disabled in the database where they should be enabled. It appeared that an update script, that is run every night, first disables all triggers on a couple of tables. Then does what it needs to do, without the overhead of the trigger […]<\/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-239","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\/239","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=239"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/239\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}