Oracle触发器6-管理触发器

1、禁用，启用，以及删除触发器

alter trigger trigger_name disable;

alter trigger trigger_name enable;

drop trigger trigger_name;

举例：禁用或者启用某个表上的全部触发器

create or replace PROCEDURE settrig(tab    IN VARCHAR2,
                                    sch    IN VARCHAR DEFAULT NULL,
                                    action IN VARCHAR2) IS
  l_action       VARCHAR2(10) := UPPER(action);
  l_other_action VARCHAR2(10) := 'DISABLED';
BEGIN
  IF l_action = 'DISABLE' THEN
    l_other_action := 'ENABLED';
  END IF;
  FOR rec IN (SELECT trigger_name
                FROM user_triggers
               WHERE table_owner = UPPER(NVL(sch, USER))
                 AND table_name = tab
                 AND status = l_other_action) LOOP
    EXECUTE IMMEDIATE 'ALTER TRIGGER ' || rec.trigger_name || ' ' ||
                      l_action;
  END LOOP;
END;

2、创建禁用的触发器

create or replace TRIGGER just_testing
  AFTER INSERT ON abc DISABLE
BEGIN
  NULL;
END;
--注意，重新编译已经禁用的触发器，会激活触发器。

3、与触发器相关的数据字典视图

DBA_TRIGGERS

ALL_TRIGGERS

USER_TRIGGERS

相关详细信息请参考oracle reference

4、检查触发器的有效性

SQL> SELECT object_name,
  object_type,
  status
  FROM user_objects
  WHERE object_name = 'INVALID_TRIGGER';