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!
