Re: Disable Trigger in Stored Proc

Hi,

you could use an PL/SQL variable that the triggers check before they execute. In a package declare: trigger_disabled boolean := false;

in each trigger put in an if-test:

begin
  if trigger_disabled = false
  then
    <trigger body>
  end if;
end;

Whenever you need to disable triggers from your program you then do:

trigger_disabled := true;
<update / insert / delete>
trigger_disabled := false;

Rgds
Steinar Heggelund

Scott Metro (smetro_at_world.std.com) wrote:
: I have an application that needs to disable triggers before it runs a
: particular process. Is it possible to write a stored procedure that can
: disable triggers. I have tried, using the following syntax, but it
: compiles with errors:
 

: CREATE OR REPLACE PROCEDURE SP_TEST AS
: BEGIN
: ALTER TABLE TEST2 DISABLE ALL TRIGGERS;
: END;
 
: Any help would be greatly appreciated.
 

: Thanks,
 

: nospam_smetro_at_world.std.com
: (Take out the "nospam_" before using the address)

--

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.