Posted by 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
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')
Learning is not a spectator sport 
Hello Connor,
While this one will take care of achieving functionality of disabling trigger from doing work for a session, the trigger will still be fired, right? This still does not help when, say, I want to DELETE a large chunk of (old) data as part of batch operations and mere presence of trigger prevents me from running DELETE in parallel.
How can I address this requirement?
parallel DML locks the table anyway, so disabling the trigger should not be an issue because you’ve already effectively blocked all access to the table anyway.
but alternatively you could use dbms_parallel_execute to simulate the parallel DML.