Trigger 中的OLD,和NEW, 參數(shù)。

 

在  INSERT 時(shí)，只有NEW,OLD為空，DELETE時(shí)，只有OLD,NEW為空。update時(shí)都有啦

--這是寫的第一個(gè)觸發(fā)器，紀(jì)念下。

CREATE OR REPLACE TRIGGER TRG_SXYK_ST_SNAPSHOOT
  BEFORE INSERT OR UPDATE OR DELETE ON BMS_ST_QTY_LST
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
       
DECLARE

  erpcompanyid varchar2(50);
  zxgoodsid varchar2(50);
  var_goodsname varchar2(50);
  issaved number(1);
  oldstqty number(10);
  pragma autonomous_transaction;
  var_goodsid NUMBER(10);
  var_storageid NUMBER(10);
 
BEGIN
 
  BEGIN

  --不管增刪改 ，找到 stid
  SELECT nvl(:NEW.Storageid,:old.Storageid) INTO var_storageid FROM dual;
 
  IF  var_Storageid IN(5,2922) THEN
   
      --不管增刪改 ，找到 goodsid
      SELECT nvl(:new.Goodsid,:old.Goodsid) INTO var_goodsid FROM dual;
     
      select goodsname,zx_goodsid INTO var_goodsname,zxgoodsid from pub_goods WHERE goodsid = var_goodsid;
      select max(value) into erpcompanyid from Sys_npbusi_config where keyword = 'ERPCOMPANYID';
     
           -- 驗(yàn)證 省貨品編碼，配送企業(yè)是否為空，為空則不插入
      IF zxgoodsid is not null and erpcompanyid is not null THEN
       
         select COUNT(1) into issaved FROM TB_KCB WHERE ERPDRUGID= var_goodsid ;
         select nvl(sum(goodsqty),0) into oldstqty from bms_st_qty_lst where  goodsid=var_goodsid and storageid IN (5,2922);
        
         --不管增刪改 ，如果存在則更新
         IF ISSAVED = 1 THEN

         UPDATE TB_KCB SET KCSL=(oldstqty - nvl(:OLD.GOODSQTY,0) + nvl(:NEW.GOODSQTY,0)),
         TB_KCB.UPDATETIME=SYSDATE WHERE TB_KCB.ERPDRUGID=var_GOODSID;
         COMMIT;
              
         ELSE
          
         --不管增刪改 ，如果不存在則插入
        
         insert into TB_KCB (ERPDrugID,KCSL,UpdateTime,ERPCompanyID,GoodsID,ERPProductName)
         values (var_goodsid,(oldstqty - NVL(:OLD.GOODSQTY,0) + nvl(:NEW.GOODSQTY,0)),
         SYSDATE,erpcompanyid,zxgoodsid,var_goodsname);
         COMMIT;
              
         END IF;

    END IF;
    
  END IF;
 
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
 
END;
/

（2）監(jiān)管碼上傳重復(fù)時(shí)提示重復(fù)，js.ajax callback 處理response,但是成功時(shí)，沒有返回response 標(biāo)簽xml數(shù)據(jù)，導(dǎo)致在 IE瀏覽器中報(bào)錯(cuò)（對象為空），頁面空白。
火狐中沒事~。。- - ！>完善代碼，上傳成功也要返回reponse 標(biāo)簽xml數(shù)據(jù) <response>ok</response>

 

 

一個(gè)事務(wù)邏輯，一定要考慮周全，”切記，切記！“