我的trigger的例子
时间：2010-08-26 来源：snailshen
CREATE OR REPLACE TRIGGER dsmpsms_sort_tri
BEFORE INSERT ON tibco3.swi_in
FOR EACH ROW
DECLARE
isExist NUMBER;
tempMsg varchar2(128);
BEGIN
--deal new's dsmp data
isExist := 0;
SELECT count(1) INTO isExist FROM cboss.DSM_SWI_MT_WAIT t where t.MSISDN=:new.phonenum and t.EXTEND_CODE=:new.termid_new
and t.STATUS='U';
if(isExist<>0) then
tempMsg := :new.msg;
update cboss.dsm_swi_mt_wait set status = 'R', rsp_time = to_char(sysdate,'YYYYMMDDHH24MISS'), --rsp_msg=:new.msg
rsp_msg=substr(tempMsg,length(:new.termid_new)+1,length(tempMsg)-length(:new.termid_new))
where status = 'U' and MSISDN = :new.phonenum and EXTEND_CODE = :new.termid_new;
--work 程序处理swi_in表里的status>0的数据,把status赋值为-999,为了work程序不会处理这部分数据
:new.status := -999;
end if;
--deal old's dsmp data ,error
--不能同时操作触发器的表
--insert into tibco3.swi_in_bak select * from tibco3.swi_in where id<>:new.id and status=-999 ;
--delete from tibco3.swi_in where id <> :new.id and status=-999;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END dsmpsms_sort_tri;
BEFORE INSERT ON tibco3.swi_in
FOR EACH ROW
DECLARE
isExist NUMBER;
tempMsg varchar2(128);
BEGIN
--deal new's dsmp data
isExist := 0;
SELECT count(1) INTO isExist FROM cboss.DSM_SWI_MT_WAIT t where t.MSISDN=:new.phonenum and t.EXTEND_CODE=:new.termid_new
and t.STATUS='U';
if(isExist<>0) then
tempMsg := :new.msg;
update cboss.dsm_swi_mt_wait set status = 'R', rsp_time = to_char(sysdate,'YYYYMMDDHH24MISS'), --rsp_msg=:new.msg
rsp_msg=substr(tempMsg,length(:new.termid_new)+1,length(tempMsg)-length(:new.termid_new))
where status = 'U' and MSISDN = :new.phonenum and EXTEND_CODE = :new.termid_new;
--work 程序处理swi_in表里的status>0的数据,把status赋值为-999,为了work程序不会处理这部分数据
:new.status := -999;
end if;
--deal old's dsmp data ,error
--不能同时操作触发器的表
--insert into tibco3.swi_in_bak select * from tibco3.swi_in where id<>:new.id and status=-999 ;
--delete from tibco3.swi_in where id <> :new.id and status=-999;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END dsmpsms_sort_tri;
相关阅读 更多 +
