2 u010791853 u010791853 于 2015.07.13 11:09 提问

oracle 修改触发:NEW值怎么是0?? 10C

触发器代码:
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?

1个回答

Tiger_Zhao
Tiger_Zhao   Rxr 2015.07.14 11:33

看代码没有问题。你不会是没有符合条件(ISDEL = 0)的M_SPILL_TYPE,最后没有插入新数据吧?

Tiger_Zhao
Tiger_Zhao 回复苦味_kuwei: 检查下是否受其它的触发器影响。你可以在这个触发器中把插入T_STOCK_DETAIL改为另外一个相同列的表,验证插入的数据。
2 年多之前 回复
u010791853
u010791853 数据成功插入进去了,但是因为这个:NEW.STOCK_ID等于0,使得我操作另一张表的时候是错误的。
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片