Thursday 6 September 2018

Create a query for MYSQL Trigger

First i have 2 tables named "item" and "buy_item" //the "stock" column is in item table and the "qty" column is ini buy_item table then I have SQL SERVER query to create a trigger like this
CREATE TRIGGER trigger1
ON dbo.buy_item
FOR UPDATE
AS begin
UPDATE item SET stock = stock - qty FROM deleted WHERE item.id = deleted.id
UPDATE item SET stock = stock + qty FROM inserted WHERE item.id = deleted.id
end

I need help to create the same function of this query in MYSQL query and i already do this
CREATE TRIGGER trigger1
BEFORE UPDATE ON buy_item
FOR EACH ROW
BEGIN
UPDATE item SET stock = stock - buy_item.qty WHERE item.id=buy_item.id
UPDATE item SET stock = stock + NEW.qty WHERE item.id=buy_item.id
END

but this isn't work at all, it says the syntax is wrong
maybe anyone can help about this
Thanks before

Assuming that you can't change item id in buy_item your trigger in MySql should look like this
CREATE TRIGGER trigger1
AFTER UPDATE ON buy_item
FOR EACH ROW
  UPDATE item
     SET stock = stock + NEW.qty - OLD.qty
   WHERE id = NEW.id;

Here is SQLFiddle demo

0 comments:

Post a Comment