Alter session set triggers disabled (or The personal anti-ninja assassin insurance policy)

If your database was a city, your tables would be buildings, your constraints would be the streets and motorways that connect them, your records would be the citizens that populate them, your views the nightclubs and restaurants where like-minded records go to hang out.

And triggers? Well, they'll definitely be ninjas. Think about it: easily overlooked, moving with stealth, 'firing' on command, shrouded in mystery.

Triggers, of course, are immeasurably useful. You don't need me to tell you that. However, there are times when you wish you could flip a switch and turn them off: imagine you are inserting or updating a truckload of data and cannot afford the overhead, or you are tracking a simple statement and do not want it to take any unexpected detours. 

Of course, you can knock up a quick anonymous pl/sql block to loop through all your triggers (or at least all the relevant ones) and dynamically disable them.

/*
** This block will disable all database triggers. Do NOT do this.
*/
begin
  for i in (select trigger_name
            from user_triggers) loop

            execute immediate 'alter trigger '||i.trigger_name||' disable';
  end loop;
end;

"Ladies and gentlemen, this is your pilot speaking. We will soon be flying across the Himalayas mountains. I will fly this section with my eyes closed. Because I can. Please, sit back and enjoy the complimentary peanuts and your in-flight movie, Alive."

Just because you can do a thing, does not mean that you should. You wrote those triggers for a reason; so unless you can guarantee that no other users will be using your database, switching them all off for any length of time might be a tad unwise. What you need in those circumstances is the ability to disable triggers for only your session. It'll be like your personal anti-ninja assassin insurance policy.

Unfortunately, ALTER SESSION SET TRIGGERS DISABLED; is not a valid Oracle command. So until it is, we are going to need to create a substitute for it.

The easiest way to do this would be to create a session variable, a boolean that you can switch on or off, and then edit your triggers to only fire dependent on the state of this variable.

The first bit is easy. A variable in the specification section of a database package makes a perfect session-specific boolean.

create or replace package trg_pkg as
   skip_trigger  boolean := FALSE;
end trg_pkg;

Now all we need do is add a line as the first bit of executable code in our triggers checking the state of our trg_pkg.skip_trigger variable. If it is TRUE, we exit the trigger without executing any code; if not, we fire the trigger as normal. Locate the triggers you may want to switch off and add the following to them:

 
...
BEGIN
  if trg_pkg.skip_trigger then return; end if; -- do not execute the trigger.
...

And that's it. Whenever you do not want triggers to fire in your session, all you need do is flip your trg_pkg.skip_trigger switch. The triggers won't fire for whatever commands you carry out, but will work as normal for everyone else. (For security reasons, you will probably want to control which users are granted execute permissions on your trg_pkg package.)

One last thing, if you want to add the clause to all the triggers in your schema - as I have needed to do recently - you might find the following code handy.

/*
** This script will rebuild all triggers adding a clause which
** prevents the trigger from firing if trg_pkg.skip_trigger is TRUE.
** This code presumes that no trigger is longer than 32767 characters long. 

** 04/02/2012  David Njoku * Created this script.
*/

declare 
 vSql  varchar2(32767);
 vBackup  varchar2(32767);
 vString  user_source.text%type;
 
 vCLAUSE  CONSTANT varchar2(500)  := ' if trg_pkg.skip_trigger then return; end if; -- Do not execute this trigger if trg_pkg.skip_trigger is set to true. ';
 
 
begin 
 -- Find all the triggers that relate to this schema's tables.
 for i in (select at.trigger_name, at.owner
    from all_triggers at, all_objects ao
    where at.table_owner = USER
    and at.status = 'ENABLED'
    and at.trigger_name = ao.object_name
    and at.owner = ao.owner
    and ao.object_type = 'TRIGGER'
    and ao.status = 'VALID'
    and not exists (select 1
      from recyclebin
      where object_name = at.trigger_name)) loop 
    
    -- Now that we've got the trigger name and owner, we need to grab a hold of the existing code, in case we need to revert.
    vBackup := ' CREATE OR REPLACE ';
    for j in (select replace(text,chr(10),' ') text 
       from all_source
       where owner = i.owner
       and name = i.trigger_name
       and type = 'TRIGGER'
       order by line) loop 
       
       vBackup := vBackup||chr(10)||j.text;
    end loop;
    
    /*
    ** We now need to start building our new version of the trigger.  The plan is to add our if clause immediately after the BEGIN 
    ** Some triggers will contain more than 1 BEGIN. Since we cannot tell which is the correct one, we'll need to add our clause after
    ** all of them.
    */

    vSql := ' CREATE OR REPLACE ';
    for j in (select replace(text,chr(10),' ') text 
       from all_source
       where owner = i.owner
       and name = i.trigger_name
       and type = 'TRIGGER'
       order by line) loop 
       
       -- do we need to add our clause here?
       if replace(upper(j.text),' ','') like '%BEGIN' then 
        vSql := vSql||chr(10)||j.text||chr(10)||vCLAUSE;
       else 
        vSql := vSql||chr(10)||j.text;
       end if;
    end loop; 
    
    -- Now we've got our trigger we should try building it.  
    begin 
     execute immediate vSql;
    exception 
     when others then 
     dbms_output.put_line(i.trigger_name||' '||sqlerrm);
    end; 
    
    -- If the trigger was not built successfully for whatever reason we should revert to the orginal.
    for j in (select 1
      from all_triggers
      where trigger_name = i.trigger_name
      and owner = i.owner
      and status = 'DISABLED') loop 
      
      begin   
       execute immediate vBackup;
      exception 
       when others then 
       dbms_output.put_line('BACKUP '||i.trigger_name||' '||sqlerrm);
      end;
    end loop; 
 end loop;
end;
/