Dumb triggers part 2 – session level control

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')

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s