Creating a trigger in mysql

Question

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

I think you need to SELECT INTO within stored procedures or triggers. You can't just have a select hanging by itself. Also, double check whether you have to use := instead of = — Sebas
– Sebas, Commented Jun 3, 2013 at 19:47

peterm · Accepted Answer · 2013-06-03 20:04:28Z

1

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 ;)

edited Jun 3, 2013 at 20:04

answered Jun 3, 2013 at 19:48

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aimad Majdou Over a year ago

What the New stands for ?

peterm Over a year ago

It's an analog for inserted but only for one row that is being inserted. That's why it reads FOR EACH ROW ...

Collectives™ on Stack Overflow

Creating a trigger in mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related