MySQL BEFORE UPDATE TRIGGER giving ERROR

问题:

I've been working on a trigger for MySQL for a couple hours now, and I can't figure out what's wrong.

Here is my table structure:

CREATE TABLE IF NOT EXISTS `RentalVideo` (
  `OrderID` int(11) NOT NULL,
  `VideoBarcode` int(11) NOT NULL,
  `RentalReturned` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`OrderID`,`VideoBarcode`),
  KEY `RentalVideo_VideoBarcode_FK` (`VideoBarcode`)
)

Here's some sample data:

INSERT INTO `RentalVideo` (`OrderID`, `VideoBarcode`, `RentalReturned`) VALUES
(1, 223823, 0),
(1, 447956, 0),
(3, 705481, 0),
(4, 988908, 0),
(5, 143375, 0);

Here's the trigger that's not working:

CREATE
    TRIGGER `RENT_FIVE_VIDEOS_MAX` BEFORE INSERT
    ON `bollywoo_video`.`RentalVideo`
FOR EACH ROW BEGIN
    -- variable declarations
    DECLARE vRentedVideos int;
    DECLARE vCustomer int;
    -- trigger code

    SELECT RentalOrder.CustID
    FROM RentalOrder
    WHERE RentalOrder.OrderID = NEW.OrderID
    INTO vCustomer;

    SELECT COUNT(*)
    FROM RentalOrder, RentalVideo
    WHERE RentalOrder.CustID = vCustomer
    AND RentalVideo.RentalReturned = 0
    AND RentalOrder.OrderId = RentalVideo.VideoID
    INTO vRentedVideos;

    IF vRentedVideos >= 5 THEN 
        CALL RAISE_APPLICATION_ERROR(-2000, 'Cannot checkout more than 5 videos');
    END IF;

END

And last but not least, this is the error I'm getting:

Error
SQL query:

CREATE TRIGGER `RENT_FIVE_VIDEOS_MAX` BEFORE INSERT ON  `bollywoo_video`.`RentalVideo` 
FOR EACH
ROW BEGIN -- variable declarations

DECLARE vRentedVideos INT;


MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6 

The error appears to be occurring right before DECLARE vRentedVideos int;

回答1:

Remove semicolon and try this

DELIMITER $$

CREATE
TRIGGER `RENT_FIVE_VIDEOS_MAX` BEFORE INSERT
ON `bollywoo_video`.`RentalVideo`
FOR EACH ROW BEGIN

 ...
END$$
DELIMITER ;