Giles Hunt Giles Hunt - 3 months ago 34
MySQL Question

MYSQL trigger syntax - missing semicolon

I'm relatively new to MYSQL from an MS SQL background. Can someone please point out why I'm getting syntax errors here please? I'm sure I've done something very obviously wrong but I just can't see it:

I'm getting a missing semicolon syntax error on line 8:

CREATE TRIGGER trg_InsertProductWatchListPriceHistory
AFTER UPDATE
ON ProductWatchlist FOR EACH ROW
BEGIN
INSERT INTO ProductWatchListPriceHistory
(ProductWatchlistID,Price)
VALUES
(ProductWatchlistID,New.ProductPrice);
END;


Any help greatly appreciated.

Answer

You need to change your default delimiter to something else than ;. Otherwise your definition ends at the first ; which would make it incomplete.

delimiter |
CREATE TRIGGER trg_InsertProductWatchListPriceHistory
AFTER UPDATE
ON ProductWatchlist FOR EACH ROW
BEGIN
   INSERT INTO ProductWatchListPriceHistory (ProductWatchlistID, Price) 
   VALUES (New.ProductWatchlistID, New.ProductPrice);
END
|
delimiter ;