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