CODEWITHSUNDEEP
oracle-databasestored-procedurestriggers
Giuseppe
Giuseppe

Reputation: 101

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

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

example (pseudocode):

MyProcedure{

    disable MyTrigger;
    
    //doStuff
    
    enable MyTrigger;

};

Upvotes: 10

Views: 24708

Answers (1)

Ollie
Ollie

Reputation: 17538

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.

Upvotes: 17

Related Questions