Struggling with MySQL trigger

Question

I want to create a trigger that inserts the total sum of all the products of an order into payments, but I'm facing syntax errors. Here's what I wrote so far:

CREATE TRIGGER set_total BEFORE INSERT on payments 
FOR EACH ROW 
BEGIN

DECLARE id INT(10);

DECLARE temp INT(10);

SET id:=new.Order;

SELECT SUM(Price) into temp

FROM products , order_products

WHERE order_products.Order LIKE id AND products.Products_ID=order_products.Products;

SET new.Total:=temp;

END;

I can't figure out my syntax error, please help.

EDIT: #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 '' at line 2

Answer 1

In multi-statement triggers, procedures, etc... you need to temporarily override the delimiter, so MySQL does not think the first ; it runs across is ending the trigger/procedure/etc...

DELIMITER $$

CREATE TRIGGER set_total BEFORE INSERT on payments 
FOR EACH ROW 
BEGIN

DECLARE id INT(10);
DECLARE temp INT(10);

SET id:=new.Order;

SELECT SUM(Price) into temp
FROM products , order_products
WHERE order_products.Order LIKE id AND products.Products_ID=order_products.Products;

SET new.Total:=temp;

END$$

DELIMITER ;

Sidenote: This is generally considered a better form of the query you use to calculate the sum:

SELECT SUM(Price) into temp
FROM products
INNER JOIN order_products ON products.Products_ID=order_products.Products
WHERE order_products.Order = id;

Also: careful with declaring variable names that could be ambiguously interpreted as field names; if products and/or order_products have an id field, then that "where" condition could cause unexpected results; at best it would still make it unnecessarily difficult to read.