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>
一个事务逻辑，一定要考虑周全，”切记，切记！“