Can I temporarily disable a trigger in an oracle stored procedure?
example (pseudocode):
MyProcedure{
disable MyTrigger;
//doStuff
enable MyTrigger;
};
You can issue DDL such as "ALTER TRIGGER" statements via dynamic SQL using the EXECUTE IMMEDIATE syntax.
A description of that 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 the dynamic SQL using a VARCHAR variable too if you like:
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 then you should also look into the package DBMS_ASSERT to wrap the triggername and help harden your code against SQL injection attacks.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With