CODEWITHSUNDEEP
mysqlsqlif-statementtriggers
Mac Luc
Mac Luc

Reputation: 1001

Mysql trigger doing calculations in 2 tables?

Im trying to make a trigger that has to do a subtraction if order_status changes from 0 to 1 or 2. The value of quantity in the updated order_status row should be subtracted from quantity_in_stock in another table. This is what ive tried, but its not working for me.

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

Upvotes: 0

Views: 99

Answers (1)

Rahul
Rahul

Reputation: 77876

Unless you have defined your own sum function, the way you are using is wrong

instead of set quantity_in_stock = sum(quantity_in_stock - orderQuantity)

it should be set quantity_in_stock = sum(quantity_in_stock) - sum(orderQuantity)

But again, you can't use aggregate function directly like this unless it's in having clause.

What you can do, declare two variable -> fetch the sum separately and store them to variable -> use them like

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

Upvotes: 1

Related Questions