Trigger 中的OLD,和NEW, 參數。
在 INSERT 時，只有NEW,OLD為空，DELETE時，只有OLD,NEW為空。update時都有啦
--這是寫的第一個觸發器，紀念下。
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';
-- 驗證 省貨品編碼，配送企業是否為空，為空則不插入
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）
監管碼上傳重復時提示重復，js.ajax callback 處理response,但是成功時，沒有返回response 標簽xml數據，導致在 IE瀏覽器中報錯（對象為空），頁面空白。
火狐中沒事~。。- - ！>完善代碼，上傳成功也要返回reponse 標簽xml數據 <response>ok</response>
一個事務邏輯，一定要考慮周全，”切記，切記！“