MySQL trigger after insert update

Question

I have following stored procedure

DELIMITER //
CREATE TRIGGER OrderDetail_AFTER_INSERT AFTER INSERT ON OrderDetail
FOR EACH ROW
BEGIN
  UPDATE Order SET total = total + NEW.subtotal WHERE id = NEW.orderid;
END;
//
DELIMITER ;

the error I am getting is

ERROR 1064 <4200>: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version fir the right syntax to use ner 'Order SET total = total + NEW.subtotal WHERE id = NEW.orderid; END' at line 5

any idea? Please help

Answer 1

Order is a reserved word. Please put backquotes around it

DELIMITER //
CREATE TRIGGER OrderDetail_AFTER_INSERT AFTER INSERT ON OrderDetail
FOR EACH ROW
BEGIN
  UPDATE `Order` SET total = total + NEW.subtotal WHERE id = NEW.orderid;
END;
//
DELIMITER ;

Give it a Try !!!

Answer 2

It seems that you are using 'Order' as a table name. In MySql, 'order' is a reserve keyword so avoid using keywords as table names. This Link has already a similar bug reported here. So change the name of your table name 'Order' to any other or give a quote to it like 'Order'.

DELIMITER //
CREATE TRIGGER OrderDetail_AFTER_INSERT AFTER INSERT ON OrderDetail
FOR EACH ROW
BEGIN
  UPDATE `Order` SET total = total + NEW.subtotal WHERE id = NEW.orderid;
END;
//
DELIMITER ;