0

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.

Community
  • 1
  • 1
Mark
  • 8,046
  • 15
  • 48
  • 78

1 Answers1

2

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.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Thank you for the reply, but sir, I don't really have a background on cursor, do you have anything like a simple update script? – Mark Jan 28 '13 at 05:21
  • There is no update script @Blackhat, you _have_ to do it this way or run the SELECT given and copy and paste the results before executing them. – Ben Jan 28 '13 at 08:09
  • I see, that means I have to work on it manually. As in one by one right? – Mark Jan 28 '13 at 08:11
  • And Thank you for sharing the [SQL Findle](http://www.sqlfiddle.com/). The tool was very useful. – Mark Jan 28 '13 at 08:13