Geek Answers Handbook

Can I temporarily disable a trigger in an oracle stored procedure?

Is it possible to temporarily disable a trigger in an oracle stored procedure?

example (pseudo code):

MyProcedure{ disable MyTrigger; //doStuff enable MyTrigger; };

thanks in advance. Giuseppe

+4
source share
1 answer

You can issue DDLs such as ALTER TRIGGER statements through dynamic SQL using the EXECUTE IMMEDIATE syntax.

Description of this is here: http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807/13_elems017.htm

 PROCEDURE myProcedure IS BEGIN EXECUTE IMMEDIATE 'ALTER TRIGGER triggername DISABLE'; -- Do work EXECUTE IMMEDIATE 'ALTER TRIGGER triggername ENABLE'; EXCEPTION WHEN OTHERS THEN -- Handle Exceptions END myProcedure;

You can build dynamic SQL using the VARCHAR variable if you want:

 PROCEDURE myProcedure IS v_triggername VARCHAR2(30) := 'triggername'; BEGIN EXECUTE IMMEDIATE 'ALTER TRIGGER '||v_triggername||' DISABLE'; -- Do work EXECUTE IMMEDIATE 'ALTER TRIGGER '||v_triggername||' ENABLE'; EXCEPTION WHEN OTHERS THEN -- Handle Exceptions END myProcedure;

If you do this, you should also look into the DBMS_ASSERT package to wrap the trigger name and help strengthen your code against SQL injection attacks.

+8
source

More articles:


All Articles