In the previous post, I pontificated about triggers that "lock you in" to having them fire, which can create dramas when it comes to doing data patching.
Maybe you can design your application around this, but if you cant, the last thing you want to be doing is having to take an outage so that you can disable the trigger whilst you do your data maintenance. Ideally you want the trigger to fire as per normal, except in your session.
And that’s actually pretty easy to do. A simple example is below
SQL> create or replace 2 package TRIGGER_CTL is 3 4 -- 5 -- Session level control of triggers for data patching etc 6 -- 7 8 -- add a trigger to NOT fire for this session 9 procedure disable(p_trigger_name varchar2); 10 11 -- reinstate normal trigger operation for this session 12 procedure enable(p_trigger_name varchar2); 13 14 -- reinstate all triggers for this session 15 procedure enable_all; 16 17 -- return if trigger is active in this session (which of course is the default) 18 function enabled_in_session(p_trigger_name varchar2) return boolean; 19 20 end; 21 / Package created. SQL> create or replace 2 package body TRIGGER_CTL is 3 4 type t_disabled_triggers is table of number 5 index by varchar2(30); 6 7 g_disabled_triggers t_disabled_triggers; 8 9 procedure disable(p_trigger_name varchar2) is 10 begin 11 g_disabled_triggers(upper(p_trigger_name)) := 1; 12 end; 13 14 procedure enable(p_trigger_name varchar2) is 15 begin 16 if g_disabled_triggers.exists(upper(p_trigger_name)) then 17 g_disabled_triggers.delete(upper(p_trigger_name)); 18 end if; 19 end; 20 21 procedure enable_all is 22 begin 23 g_disabled_triggers.delete; 24 end; 25 26 function enabled_in_session(p_trigger_name varchar2) return boolean is 27 begin 28 return not g_disabled_triggers.exists(upper(p_trigger_name)); 29 end; 30 31 end; 32 / Package body created.
Once we’ve got that little utility coded up, its easy to get session level control over triggers, simply by adding a check
CREATE OR REPLACE TRIGGER MY_TRG BEFORE INSERT OR UPDATE ON MY_TABLE FOR EACH ROW BEGIN if trigger_ctl.enabled_in_session('MY_TRG') then <my original trigger body code> end if; END; /
And to control the trigger at session level, its then just a call to your API
SQL> exec trigger_ctl.disable('MY_TRG') SQL> -- then your data maintenance SQL> exec trigger_ctl.enable('MY_TRG')