问题描述

oracle 修改触发:NEW值怎么是0？？

触发器代码：
create or replace trigger TR_T_SPILL_LOSS_LIST
BEFORE update on t_spill_loss_list

for each row
declare
M_SPILL_TYPE VARCHAR2(30);
M_SHOP_ID NUMBER(18);
begin
M_SPILL_TYPE := '';
M_SHOP_ID := 0;

SELECT MAX(SPILL_TYPE), MAX(SHOP_ID) INTO M_SPILL_TYPE, M_SHOP_ID
FROM T_SPILL_LOSS
WHERE ISDEL = 0 AND SPILL_ID = :NEW.SPILL_ID AND ROWNUM = 1;

IF (NVL(:NEW.ISAUDITING, 0) = 1) AND (NVL(:OLD.ISAUDITING, 0) = 0) AND (M_SPILL_TYPE='TYPE_1') THEN
INSERT INTO T_STOCK_DETAIL(STOCK_DETAIL_ID, STOCK_DATETIME, STOCK_USER_ID, STAT_ID, SELLER_ID, SKU_ID,
STOCK_ID, CHARGE_QTY, CHARGE_QTY2, LIST_ID, STOCK_DETAIL_MODE, ISDEF)
VALUES(SQ_STOCK_DETAIL_ID.NEXTVAL, SYSDATE, :NEW.CREATE_USER_ID, M_SHOP_ID, :NEW.SELLER_ID, :NEW.SKU_ID,

:NEW.STOCK_ID, :NEW.SPILL_LOSS_QTY, NULL, :NEW.SPILL_LOSS_LIST_ID, 'MODE_BY', 0);
ELSIF (NVL(:NEW.ISAUDITING, 0) = 0) AND (NVL(:OLD.ISAUDITING, 0) = 1) AND (M_SPILL_TYPE='TYPE_1') THEN
INSERT INTO T_STOCK_DETAIL(STOCK_DETAIL_ID, STOCK_DATETIME, STOCK_USER_ID, STAT_ID, SELLER_ID, SKU_ID,
STOCK_ID, CHARGE_QTY, CHARGE_QTY2, LIST_ID, STOCK_DETAIL_MODE, ISDEF)
VALUES(SQ_STOCK_DETAIL_ID.NEXTVAL, SYSDATE, :NEW.CREATE_USER_ID, M_SHOP_ID, :NEW.SELLER_ID, :NEW.SKU_ID,

:NEW.STOCK_ID, NULL, :NEW.SPILL_LOSS_QTY, :NEW.SPILL_LOSS_LIST_ID, 'MODE_BY', 0);
ELSIF (NVL(:NEW.ISAUDITING, 0) = 1) AND (NVL(:OLD.ISAUDITING, 0) = 0) AND (M_SPILL_TYPE='TYPE_2') THEN
INSERT INTO T_STOCK_DETAIL(STOCK_DETAIL_ID, STOCK_DATETIME, STOCK_USER_ID, STAT_ID, SELLER_ID, SKU_ID,
STOCK_ID, CHARGE_QTY, CHARGE_QTY2, LIST_ID, STOCK_DETAIL_MODE, ISDEF)
VALUES(SQ_STOCK_DETAIL_ID.NEXTVAL, SYSDATE, :NEW.CREATE_USER_ID, M_SHOP_ID, :NEW.SELLER_ID, :NEW.SKU_ID,

:NEW.STOCK_ID, NULL, :NEW.SPILL_LOSS_QTY, :NEW.SPILL_LOSS_LIST_ID, 'MODE_BS', 0);

ELSIF (NVL(:NEW.ISAUDITING, 0) = 0) AND (NVL(:OLD.ISAUDITING, 0) = 1) AND (M_SPILL_TYPE='TYPE_2') THEN
INSERT INTO T_STOCK_DETAIL(STOCK_DETAIL_ID, STOCK_DATETIME, STOCK_USER_ID, STAT_ID, SELLER_ID, SKU_ID,
STOCK_ID, CHARGE_QTY, CHARGE_QTY2, LIST_ID, STOCK_DETAIL_MODE, ISDEF)
VALUES(SQ_STOCK_DETAIL_ID.NEXTVAL, SYSDATE, :NEW.CREATE_USER_ID, M_SHOP_ID, :NEW.SELLER_ID, :NEW.SKU_ID,

:NEW.STOCK_ID, :NEW.SPILL_LOSS_QTY, NULL, :NEW.SPILL_LOSS_LIST_ID, 'MODE_BS', 0);
END IF;
end TR_T_SPILL_LOSS_LIST;

执行触发器代码：
UPDATE T_SPILL_LOSS_LIST
SET STOCK_ID =6722
, ISAUDITING = 0
WHERE SPILL_LOSS_LIST_ID =13643;

我明明把STOCK_ID修改成6722了，可是：NEW.STOCK_ID还是等于0？

解决方案

看代码没有问题。你不会是没有符合条件（ISDEL = 0）的M_SPILL_TYPE，最后没有插入新数据吧？