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
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
buy_item
your trigger in MySql should look like thisCREATE TRIGGER trigger1
AFTER UPDATE ON buy_item
FOR EACH ROW
UPDATE item
SET stock = stock + NEW.qty - OLD.qty
WHERE id = NEW.id;
Hello Friends! I am Ramana a part time blogger from Hyderabad.
0 comments:
Post a Comment