CODEWITHSUNDEEP
sqltriggersoracle11g
Mark
Mark

Reputation: 8431

Script to disable an oracle trigger

Before anything else, I have seen this post here in stack. It says there how to list all the triggers, what I'm asking is script to disable (not delete/remove) all the triggers.

Upvotes: 0

Views: 3193

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Interesting question. Unfortunately I can't test this locally, but something like this could potentially work. Basically, declare a CURSOR to loop through all the triggers (for my purposes, I was testing with a couple TEST triggers). Then use EXECUTE IMMEDIATE to execute dynamic sql. 

DECLARE
CURSOR cur_trigger IS
SELECT 'ALTER TRIGGER ' ||
       table_owner ||
       '.' ||
       trigger_name ||
       ' DISABLE' SQLStatement
FROM all_triggers
WHERE trigger_name like '%TEST%';
sqlText cur_trigger%ROWTYPE;

BEGIN
  OPEN cur_trigger;
  LOOP
    FETCH cur_trigger INTO sqlText;
    EXIT WHEN cur_trigger%NOTFOUND;

    EXECUTE IMMEDIATE sqlText.SQLStatement;
  END LOOP;
  CLOSE cur_trigger;
END;

And here is the SQL Fiddle. It doesn't work completely in Fiddle, but I believe that's because of Fiddle and not the code.

Good luck.

Upvotes: 2

Related Questions