Panda Panda - 2 months ago 7
MySQL Question

MySQL: Keep getting errors when creating a trigger to update between two tables

The two tables I'm trying to create a trigger between are item(itemId, title, releaseDate, itemPublisherId, retailPrice, item CategoryId, quantityInStock) and orderItem(orderId, itemId, quantity)

I'm trying to get the quantityInStock to update whenever a new item is ordered. I've tried doing this below, but it keeps giving me an error.

delimiter $$

CREATE TRIGGER quantityLeft
AFTER INSERT on orderItem
for each row begin
UPDATE item
SET quantityInStock = quantityInStock - new.quantity;
WHERE itemId = new.itemId;
end$$

delimiter ;


This will be the "test" I'll be using when I insert a new order to make sure it only updates one row. (Before adding in "WHERE itemId = new.itemId;" it would update all the quantityInStock and I don't want that)

insert into orderItem (orderId, itemId, quantity)
values ('002', 1, 3);

Answer
DROP TRIGGER IF EXISTS quantityLeft;
delimiter $$

CREATE TRIGGER quantityLeft
AFTER INSERT on orderItem
for each row begin
UPDATE item
SET quantityInStock = quantityInStock - new.quantity
WHERE itemId = new.itemId;
end$$

delimiter ;

Typo. You had an extra errant semi-colon after - new.quantity