MySQL触发器: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

Question

我在 MySQL 5.1.73 中创建触发器时遇到问题这是语法:

DELIMITER $$
CREATE TRIGGER `discount2` 
BEFORE 
INSERT ON `order_item` 
FOR EACH ROW 
DECLARE alumno INT;
DECLARE profesor INT;
DECLARE dto decimal(10,2);
SET @alumno := (SELECT user_id from `order` where `order`.id = NEW.order_id)
SET @profesor := (SELECT id_profesor from user where user.id = @alumno)
SET @dto := (SELECT descuento from descuentos
join user on descuentos.id_profesor = user.id
join producto on producto.familia=descuentos.familia
where producto.id = NEW.product_id and user.id = @profesor)

SET.NEW.descuento = SELECT CAST((((@dto)*(NEW.pvp))/100) AS DECIMAL(10,2))
$$
DELIMITER ;

但是有一些错误...

#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 'DECLARE alumno INT;
DECLARE profesor INT;
DECLARE dto decimal(10,2);
SET @alu' at line 5

有人可以帮助我吗？谢谢你

Answer 1

请注意此语法，但可能无法“工作”

drop trigger if exists discount2;

DELIMITER $$
CREATE TRIGGER `discount2` 
BEFORE 
INSERT ON `order_stage` 
FOR EACH ROW 
begin
DECLARE alumno INT;
DECLARE profesor INT;
DECLARE dto decimal(10,2);
SET alumno = (SELECT user_id from `order` where `order`.id = NEW.order_id);
SET profesor = (SELECT id_profesor from user where user.id = alumno);
SET dto = (SELECT descuento from descuentos
join user on descuentos.id_profesor = user.id
join producto on producto.familia=descuentos.familia
where producto.id = NEW.product_id and user.id = profesor);

SET NEW.descuento = (SELECT CAST((((dto)*(NEW.pvp))/100) AS DECIMAL(10,2)));
end $$
DELIMITER ;