即使给出了不同的 where 子句，MySQL 触发器也会更新所有行

Question

引用下面的触发器，

create trigger trigger_update_rows after insert on transaction for each row

DECLARE tr_type VARCHAR(15);
DECLARE tr_op VARCHAR(10);
DECLARE tr_amt FLOAT(8,2);
DECLARE tr_qnt INTEGER;
DECLARE item_id INTEGER;
SET tr_type = NEW.TRANSACTION_TYPE;
SET tr_op = (SELECT TRANSACTION_TYPE_OPERATION FROM transaction_type WHERE 
TRANSACTION_TYPE_NAME=tr_type limit 1);
SET tr_amt = NEW.TRANSACTION_AMOUNT;
SET tr_qnt = NEW.QUANTITY;
SET item_id = NEW.ITEM_ID;
IF tr_op = 'ADD' THEN
update item set QUANTITY_AVAILABLE=QUANTITY_AVAILABLE-
tr_qnt,QUANTITY_SOLD=QUANTITY_SOLD+tr_qnt,AVAILABLE_GOODS_VALUE = 
AVAILABLE_GOODS_VALUE - tr_amt,SOLD_GOODS_VALUE=SOLD_GOODS_VALUE+tr_amt 
where ITEM_ID=item_id;
ELSEIF tr_op = 'SUBTRACT' THEN
update item set QUANTITY_AVAILABLE=QUANTITY_AVAILABLE+tr_qnt,QUANTITY_BOUGHT=QUANTITY_BOUGHT+tr_qnt,AVAILABLE_GOODS_VALUE=AVAILABLE_GOODS_VALUE+tr_amt,BOUGHT_GOODS_VALUE=BOUGHT_GOODS_VALUE+tr_amt where ITEM_ID=item_id;
END IF;
END 

ITEM_ID是表项中的主键，下面是插入的事务行

insert into transaction(TRANSACTION_DATE,TRANSACTION_TYPE,ITEM_ID,QUANTITY,TRANSACTION_AMOUNT,ACCOUNT_ID,BUYER,SELLER) values ('2017-08-12','BUY',2,5,500.00,3,1,2);

是什么使上述触发器更新 item 中的所有行而不是遵循 where 子句

Answer 1

你的问题是:

where ITEM_ID = item_id;

您可能认为其中一个 item_id 以某种方式与该变量相关联。事实并非如此。

将变量重命名为 v_item_id 之类的名称，然后使用:

where item_id = v_item_id