Oracle触发器6-管理触发器


		Oracle触发器6-管理触发器
 
Oracle触发器5-Instead of触发器
http://www.zzzyk.com/database/201304/203344.html
 
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
4、检查触发器的有效性
SQL> SELECT object_name,
object_type,
status
FROM user_objects
WHERE object_name = 'INVALID_TRIGGER';