Re: Database triggers

Laurent Citton <citton_at_shom.fr> wrote:

>Does someone know if it is possible to disable database triggers for one
>session only, while other sessions still use those triggers ?

Sort of.

Consider the following:

create or replace package some_variables as

    fire_trigger boolean default TRUE;
end some_variables;
/

Create or replace trigger foobar
before insert or update or delete on foobar begin
  if ( some_variables.fire_trigger = FALSE ) then

      return;
  end if;
  ........
end;
/

Now all I would do in my application to 'disable' this trigger is to:

....

   EXEC SQL EXECUTE
   begin

      some_variables.fire_trigger := FALSE;    end;
   END-EXEC;     /* Trigger will not fire */
   Insert into foobar values ( .... );

So while there is no database command to do it you can do it yourself. Also, you only need grant execute on the some_variables package to those people who are allowed to disable the trigger. That way you prevent unauthorized people from subverting your trigger in sql*plus and so on.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Wed Oct 11 1995 - 00:00:00 CET