我的trigger的例子

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;