CODEWITHSUNDEEP
mysqltriggers
Aimad Majdou
Aimad Majdou

Reputation: 583

Creating a trigger in mysql

I'm trying to create a trigger on a table after inserting, which gonna update another table.

this is the code I tried :

delimiter |
CREATE TRIGGER augmenter_quantite_article AFTER INSERT
ON LigneInterventaire
FOR EACH ROW BEGIN
DECLARE @qte AS INTEGER;
DECLARE @code AS INTEGER;
SELECT @qte = qteInv FROM INSERTED;
SELECT @code = codeArt FROM INSERTED;
UPDATE Article SET qteArt = qteArt + @qte WHERE codeArt = @code;
END;

|

delimiter ;

but I get this error message :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@qte AS INTEGER; DECLARE @code AS INTEGER; SELECT @qte = qteInv FROM INSERTED; S' at line 4

Upvotes: 0

Views: 1034

Answers (1)

peterm
peterm

Reputation: 92785

Try

CREATE TRIGGER augmenter_quantite_article 
AFTER INSERT ON LigneInterventaire
FOR EACH ROW 
  UPDATE Article 
     SET qteArt = qteArt + NEW.qteInv
   WHERE codeArt = NEW.codeArt;

Here is SQLFiddle demo.

MySql has somewhat limited trigger implementation. There are no virtual tables inserted and deleted per se as in SQL Server. Instead you can access old and new values of a row that is being inserted or updated with keywords OLD and NEW respectively.

Since it boiled down to only one UPDATE statement you no longer need to use BEGIN END block. Another plus - you don't need change DELIMITER either ;)

Upvotes: 1

Related Questions