Can an Oracle trigger be disabled for the current session?

Question

I'd like to disable a specific trigger on a table prior to inserting data into it, but without affecting other users that may be changing data in that same table. I can't find any documented way to do this. This is Oracle 11g.

The best solution I've been able to come up with is to create a session variable and have my application set that to some value that the trigger checks for prior to doing its work.

Obligatory anti-trigger comment: I hate triggers.

Solution 1

Add a variable to an existing package spec (or create a new package):

enable_trigger boolean := true;

Surround the code in the trigger with:

if enable_trigger then

end if;

When you want to "disable" the trigger set the variable to false.

A Best Practice would be to put the variable in the body and write a set procedure and a get function.

Solution 2

Use dbms_application_info.set_client_info (link to oracle documentation) to set_client_info in the procedure and read it in the trigger.

Simple example:

SET SERVEROUTPUT ON   
declare
CI_Status VARCHAR2(25 BYTE):='';
begin
--set the value
dbms_application_info.set_client_info('qwerty');
-- the value is sent an out to CI_Status when you want to read it
DBMS_APPLICATION_INFO.READ_CLIENT_INFO (CI_Status);
--Output the value in the console 
dbms_output.put_line('Value of CI_Status is: ' || CI_Status); 
end;

In your procedure:

procedure spname is
    begin
      dbms_application_info.set_client_info('qwerty');
      --Do your update
      UPDATE tableName set a=b;
      --in case you still have the sesion opened, set it to null after
      dbms_application_info.set_client_info(null);
end;

In you trigger:

create or replace TRIGGER Triggername
 BEFORE INSERT OR UPDATE
 ON tablename
 FOR EACH ROW
declare
CI_Status VARCHAR2(25 BYTE):='';
begin
--Retrieve the value into CI_Status the variable
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(CI_Status); 
    IF INSERTING THEN
       null;
    ELSIF UPDATING THEN
          IF CI_Status = 'qwerty' then
             --Do nothing since you dont want the trigger to fire
              null;
          ELSIF CI_Status is null then
            :new.tablefield:= SYSDATE;
          END IF;
    END IF;
end Triggername;

To disable completely without worrying about concurrences

 procedure spname is
    begin    
      EXECUTE IMMEDIATE 'ALTER TRIGGER Triggername DISABLE';
      UPDATE tableName set a=b;
      EXECUTE IMMEDIATE 'ALTER TRIGGER Triggername ENABLE';
   end;

Solution 3

I dont think that disabling trigger is possible for a particular session is possible in oracle or other rdbms .

My solution is that ,if you know the CURRENT_USER or the session_id from which you login ,than you can put a condition in the trigger .

  IF SYS_CONTEXT ('USERENV', 'CURRENT_USER') <> '<XYZ>' THEN
    --do the operation

  END IF; 

This condition you need to put in your trigger

Solution 4

Maybe Oracle Editions are useful for this purpose? Create a different trigger in another edition and change the session to use that edition.

http://www.oracle-base.com/articles/11g/edition-based-redefinition-11gr2.php#editions

I must confess that I have no practical experience with editions.

View more solutions

Comments

[David Aldridge]

+1 for hating triggers

[David Aldridge]

@BobJarvis as part of the business logic for an application, I think triggers suck. For out-of-application logging and monitoring though, they're great. But of course what people use them for is the former, mostly I suppose because they had a college assignment that required using them as part of business logic, or even worse to enforce a constraint. Their assignment didn't include debugging it afterwards.

[APC]

Exactly. The whole point about triggers is that they fire whenever the event occurs.

[Gaurav Soni]

@Rene: Thanks for the possible alternative ,but i am not familiar with this approach .Will need to read and do some handson on this .+1

[redcayuga]

You may get this to work for you but that is not the intended use of Editions.

[redcayuga]

So user XYZ will never execute the code. Is this a good idea?

[Gaurav Soni]

@redcayuga:It depends on the condition,under what event the programmer wants to execute the code,I just advised him that you cannot diable trigger for a particular session ,and to achieve his requirement he need to change the trigger with IF condition,but in my opinion,you answer this question better .