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.

Answer 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.

Answer 2

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