|
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>
|