Mt Lindsay

Sunday, June 5, 2016

How to Disable Database Triggers for a Single Session

The Problem:
Database triggers can come in very handy and be used to solve a myriad of problems.  However, at times, their effects may not be desired. As an example, say there is a before-update row-level trigger that prevents updates to a row when its status code equals ‘CLOSED’.  We all know that sooner or later, a customer will need to change a row that has a status of ‘CLOSED’. Of course the trigger can be disabled prior to running the update script, such as:

       
begin

  execute immediate 'alter trigger bu_my_table disable';

  update my_table
     set x = 32
   where x = 3.2
     and status_code = ‘CLOSED’
     and create_date = ‘14-jul-2014’;

  execute immediate 'alter trigger bu_my_table enable';

end;
       
 

The problem with this approach is that the trigger logic is absent for updates occurring in other sessions as well. This may be problematic, or depending on the logic that gets skipped, catastrophic. It could help to just run your script when the likelihood of other updates occurring is lower.  While that approach may reduce risk, it's still far less than ideal. 

There is a better, safer way! The remainder of this article will explain how you can effectively disable triggers for just the single session used by the script.

The Solution:
The solution takes advantage of the fact that package variables are stored in the user global area (UGA). Data stored in the UGA is specific to a user’s session and can only be seen by programs the run in that same session. Therefore, if an update script running in session XYZ stores a value in a package variable, then that variable’s value can be interrogated by a trigger also running in session XYZ to determine whether to run the logic in the trigger.  The process is described in detail below.
  • You have determined that a script needs to skip trigger logic while executing DML.
  • The script sets a package variable to a prescribed value prior to executing DML.
  • The script executes the DML, which causes the trigger to fire.
  • Before the trigger logic executes, the trigger inspects the package variable for the presence of the prescribed value.  
  • If the value is found in the package variable, the logic is skipped, otherwise the logic is executed.
To implement these steps, do the following:
  • Create a package with a PL/SQL table indexed by varchar2 to represent a list of programs. 
  • Add two procedures to the package; enable and disable.
    • disable adds a value to list represented by the PL/SQL table
    • enable removes a value from the list represented by the PL/SQL table
  • Add a function called is_enabled to the package
    • is_enabled checks for the presence of a value in the list of programs
  • Alter your trigger source code to call is_enabled.
  • Alter any future scripts that need to skip trigger logic by calling disable before executing DML, and then call enable afterward.
The source code for step 1 is below.

       

CREATE OR REPLACE PACKAGE program_control_pkg
IS
  PROCEDURE disable (in_program VARCHAR2);

  PROCEDURE enable (in_program VARCHAR2);

  FUNCTION is_enabled (in_program VARCHAR2) RETURN BOOLEAN;

END;  -- Package spec

CREATE OR REPLACE PACKAGE BODY program_control_pkg
IS
  type program_tab_typ is table of varchar2(1)
  index by varchar2(4000);

  program_tab program_tab_typ;

  procedure disable( in_program varchar2 ) 
  is
  begin

    program_tab( in_program ) := 'Y';

  end disable; 

  procedure enable( in_program varchar2 ) 
  is
  begin

    program_tab.delete( in_program );

  end enable;

  function is_enabled( in_program varchar2 ) return boolean 
  is
    v_dummy varchar2(1);
    answer boolean;

  begin

    answer := true;

    begin

       v_dummy := program_tab(in_program);
       answer := false;

    exception

      when no_data_found then
        answer := true;

    end;

    return answer;

  end;

end;
       
 

Next, any triggers that may need logic skipped are altered as follows.

       

create or replace trigger bu_my_table
before update on my_table
for each row

  update_not_allowed exception;

begin

  if program_control_pkg.is_enabled('bu_my_table') then

    if :old.status_code = ‘CLOSED’ and :new.status_code = ‘CLOSED’ then
      raise update_not_allowed;
    end if; 

  end if;
…

exception 
  when update_not_allowed then
  …

end;

       
 

Now, any script that updates my_table can skip trigger logic for only its session:

       

begin

  program_control_pkg.disable('bu_my_table’);

  update my_table
     set x = 32
   where x = 3.2
     and status_code = ‘CLOSED’
     and create_date = ‘14-jul-2014’;

  program_control_pkg.enable('bu_my_table');

end;
       
 

Credit goes to Steve Jun for the idea.  I was merely the code monkey responsible for implementation.

This article was first published in the Fall 2015 issue of RMOUG SQL>Update magazine.

View the magazine!

3 comments:

  1. Hi Tom, I'd preferably use tigger WHEN clause combined with context. This allows for better performance because WHEN is evaluated before trigger body.

    Kind regards,
    Daniel

    ReplyDelete