mysql - MySQL触发器在2个表中进行计算？

我试图创建一个触发器，如果​​order_status从0变为1或2，则必须进行减法。应从另一个表的quantity_in_stock中减去更新后的order_status行中的数量值。香港专业教育学院尝试过此方法，但不适用于我。

begin
   DECLARE orderQuantity INT(11);
   if old.order_status = 0 and new.order_status != 0 then
   select quantity into orderQuantity from `order` where id=new.id;
   update product_in_stock
   set quantity_in_stock = sum(quantity_in_stock - orderQuantity)
   where id=1;
   end if;
end

最佳答案

除非您定义了自己的sum函数，否则使用方法是错误的

代替set quantity_in_stock = sum(quantity_in_stock - orderQuantity)

它应该是set quantity_in_stock = sum(quantity_in_stock) - sum(orderQuantity)

但是同样，您不能像这样直接使用聚合函数，除非它在having子句中。

您可以做什么，declare two variable -> fetch the sum separately and store them to variable -> use them喜欢

DECLARE sum_quantity_in_stock INT(11);
DECLARE sum_orderQuantity INT(11);

select sum(quantity_in_stock) into sum_quantity_in_stock from sometable;
select sum(orderQuantity) into sum_orderQuantity from sometable;


set quantity_in_stock = sum_quantity_in_stock - sum_orderQuantity