CODEWITHSUNDEEP
mysqltriggers
Pranatha Halim
Pranatha Halim

Reputation: 23

Create 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

Upvotes: 2

Views: 308

Answers (3)

peterm
peterm

Reputation: 92785

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

Upvotes: 1

Pranatha Halim
Pranatha Halim

Reputation: 23

this is the error message after i write the syntax

DELIMITER $$
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$$

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for right syntax to use near 'DELIMITER $$
CREATE TRIGGER Trigger1
BEFORE UPDATE ON buy_item
FOR EACH ROW' at line 1'"

My MySQL server version is 5.5.13

Upvotes: 0

legrandviking
legrandviking

Reputation: 2414

A trigger body in mysql requires a number of SQL commands separated by a semi-colon (;). To create the full trigger code one must define its own delimiter to something else — such as $$.

DELIMITER $$
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$$

This web site as helped me in the past for syntax and other relevant issues to mysql and triggers http://www.sitepoint.com/how-to-create-mysql-triggers/

Upvotes: 0

Related Questions