Re: Disable Trigger in Stored Proc

Scott,

>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;
Take a look at the documentation for the DBMS_SQL package. It's in the Application Developer's manual if you have Oracle 7.3. I believe this code will do what you want:

...
statement_handle integer;

begin
  statement_handle := dbms_sql.open;
  dbms_sql.parse(statement_handle,'alter table test2 disable all triggers;');
dbms_sql.close;
end;

The "parse" procedure will execute any data definition language statements that are passed to it. You may want to verify my declaration of statement_handle as in integer. I don't have a manual in front of me right now.

good luck,

Jonathan

Jonathan Gennick
gennick_at_worldnet.att.net
"Brighten the Corner Where you Are" Received on Sat Mar 22 1997 - 00:00:00 CET