Disable trigger : Utility Procedure « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Procedure

SQL>
SQL> CREATE OR REPLACE PROCEDURE settrig (tab IN VARCHAR2,action IN VARCHAR2) IS
  2     v_action VARCHAR2 (10) := UPPER (action);
  3     v_other_action VARCHAR2 (10) := 'DISABLE';
  4  BEGIN
  5     IF v_action = 'DISABLE'
  6     THEN
  7        v_other_action := 'ENABLE';
  8     END IF;
  9     FOR rec IN (SELECT trigger_name
 10                   FROM user_triggers
 11                  WHERE table_owner = USER
 12                    AND table_name = UPPER (tab)
 13                    AND status = v_other_action)
 14     LOOP
 15        EXECUTE IMMEDIATE 'ALTER TRIGGER ' || rec.trigger_name || ' ' || v_action;
 16        DBMS_OUTPUT.put_line ('Set status of ' || rec.trigger_name || ' to ' || v_action);
 17     END LOOP;
 18  END;
 19  /

Procedure created.

SQL>