代码如下:
create or replace trigger sync_nas_failReason_trigger
/**
**名称:网管接口失败信息同步
**功能:spjk给工作流回单时,一并将nas回填的失败信息同步到wo的remarks字段中,以提供前台展现
**created by zdp @ 2010/6/23
**/
after update on spjk_table
for each row
declare
woRemarks wo.remarks%type; --wo备注信息
tempInfo varchar2(512); --新拼接的wo备注信息
begin
--当回填标志变为Y时,执行联动操作
if :new.backfill_flag ='Y' then
--取原wo备注信息
select wo.remarks into woRemarks from wo where wo.wo_nbr =:new.wo_nbr;
--拼接新信息
tempInfo :=substr(woRemarks||'==>'||:new.err_msg,1,512);
--dbms_output.put_line( 'tempInfo='||tempInfo);
--执行联动操作
update wo set wo.remarks =tempInfo where wo.wo_nbr =:new.wo_nbr;
end if;
end sync_nas_failReason_trigger;
测试:
--测试
select st.backfill_flag, st.wo_nbr, st.err_msg from spjk_table st where st.spjk_id ='201184234'
update wo set wo.remarks =tempInfo where wo.wo_nbr =750556
UPDATE spjk_table st set ST.BACKFILL_FLAG ='F' where st.spjk_id ='201184234'
UPDATE spjk_table st set ST.BACKFILL_FLAG ='Y' where st.spjk_id ='201184234'
select * from wo where wo.remarks ='==>业务140001未执行;处理中;||业务140069未执行;NULL;||'