Oracle trigger 应用

 

 

代码如下：

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