[Oracle整理]Trigger失效
说明：本内容是工作用到的知识点整理，来自工作中和网络。
代码于Oracle9上测试。
Trigger失效
经常发现系统里的Trigger失效（all_objects.status='INVALID'并且all_triggers.status='ENABLED'）
失效原因
涉及的一些表或者其它对象被删除或者其它操作（DDL），这时你需要重新编译或重建。所以，严密周到还是配有相应文档。举一个例子，Test1表（NUM, CREATEDATE),有一个insert触发器,当alter table Test1 add(a);那触发器变为失效，要重新创建。
同样，删除字段也会失效。
同样，修改字段的类型（Type）也会失效。
查看Trigger状态
-- 有是否有效的状态(INVALID/VALID)
select * from all_objects o
where o.object_type='TRIGGER'
AND o.object_name in ('WRITE_BACK_RCL_DETAIL_F137','WRITE_BACK_RCL_DETAIL_F138')
-- 有是否启用的状态(DISABLED/ENABLED)
select * from all_triggers t
where t.trigger_name in ('WRITE_BACK_RCL_DETAIL_F137','WRITE_BACK_RCL_DETAIL_F138')
-- 启用，但是失效了
select o.owner,o.object_name,o.status as is_VALID,t.trigger_type,t.table_name,t.column_name,t.status as is_ENABLED
from all_objects o,all_triggers t
where o.object_name=t.trigger_name
and o.status='INVALID'
and t.status='ENABLED'
重新编译Trigger
1手动编译
ALTER TRIGGER Test1_trg COMPILE
2自动编译
1）dbms_utility.compile_schema(‘user’,false);调用这个过程就会编译所有失效的过程、函数、触发器、包。dbms_utility.compile_schema参数：
2）自动失效的对象，一般会在下次调用的时候，被重新编译通过，所以不需要人工干预
3定时编译
--创建自动编译失效过程事务记录表
declare
tabcnt integer := 0;
begin
select count(*) into tabcnt from dba_tables where table_name='RECOMPILE_LOG';
if tabcnt = 0 then
execute immediate 'create table recompile_log(rdate date,errmsg varchar2(200))';
end if;
end;
/
--创建编译失效对象的存储过程
create or replace procedure recompile_invalid_objects
as
str_sql varchar2(200); --中间用到的sql语句
p_owner varchar2(20); --所有者名称，即SCHEMA
errm varchar2(200); --中间错误信息
begin
/*****************************************************/
p_owner := 'owner';/***用户名*************************/
/*****************************************************/
insert into recompile_log(rdate, errmsg) values(sysdate,'time to recompile invalid objects');
--编译失效存储过程
for invalid_procedures in (select object_name from all_objects
where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))
loop
str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_procedures.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--编译失效函数
for invalid_functions in (select object_name from all_objects
where status = 'INVALID' and object_type = 'FUNCTION' and owner=upper(p_owner))
loop
str_sql := 'alter function ' ||invalid_functions.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_functions.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--编译失效包
for invalid_packages in (select object_name from all_objects
where status = 'INVALID' and object_type = 'PACKAGE' and owner=upper(p_owner))
loop
str_sql := 'alter package ' ||invalid_packages.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_packages.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--编译失效类型
for invalid_types in (select object_name from all_objects
where status = 'INVALID' and object_type = 'TYPE' and owner=upper(p_owner))
loop
str_sql := 'alter type ' ||invalid_types.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_types.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--编译失效索引
for invalid_indexs in (select object_name from all_objects
where status = 'INVALID' and object_type = 'INDEX' and owner=upper(p_owner))
loop
str_sql := 'alter index ' ||invalid_indexs.object_name || ' rebuild';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_indexs.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
--编译失效触发器
for invalid_triggers in (select object_name from all_objects
where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner))
loop
str_sql := 'alter trigger ' ||invalid_triggers.object_name || ' compile';
begin
execute immediate str_sql;
exception
When Others Then
begin
errm := 'error by obj:'||invalid_triggers.object_name||' '||sqlerrm;
insert into recompile_log(rdate, errmsg) values(sysdate,errm);
end;
end;
end loop;
end;
/
--创建任务计划，每天早上8点整执行该任务，且保证此任务有且只有一个
declare
jobcnt integer :=0;
job_recompile number := 0;
str_sql varchar2(200);
begin
select count(*) into jobcnt from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N';
if jobcnt > 0 then
for jobs in (select job from all_jobs where what = 'recompile_invalid_objects;' and broken = 'N')
loop
str_sql := 'begin dbms_job.remove('||jobs.job||'); end;';
begin
execute immediate str_sql;
exception
When Others Then null;
end;
end loop;
end if;
--创建任务计划
dbms_job.submit(job_recompile,'recompile_invalid_objects;',sysdate,'TRUNC(SYSDATE + 1) + 8/24');
--启动任务计划
dbms_job.run(job_recompile);
end;
/
网友对上面PROCEDURE的评论：
楼主的想法是好的，但过程太过复杂，
1）oracle提供了自动编译的接口
dbms_utility.compile_schema(user,false);
调用这个过程就会编译所有失效的过程、函数、触发器、包。
2）自动失效的对象，一般会在下次调用的时候，被重新编译通过，所以不需要人工干预。
3）自动编译所有的过程还可以，但我觉得重建索引……
调试触发器
平时用PL/SQL调试PROCEDURE，右键点击PROCEDURE有Test选项。而Trigger没有，可以建立一个测试存储过程，执行到可以触发触发器的SQL时，断点跟入Step Into便可进入触发器。
学习数据
1） http://topic.csdn.net/u/20110111/10/7b9a9e0c-5708-4146-b210-4f0d1e2f0fcd.html
2） http://zhouwf0726.itpub.net/post/9689/194221
3） http://www.cnblogs.com/kerrycode/archive/2009/10/15/1582807.html
