MySQL BEFORE UPDATE TRIGGER给出错误 [英] MySQL BEFORE UPDATE TRIGGER giving ERROR

问题描述

我一直在为MySQL的触发器工作几个小时了，我不知道是什么问题。

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

这里是我的表结构：

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`)
)

一些示例数据：

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

最后但并非最不重要的是， m get：

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 

错误似乎正在 DECLARE vRentedVideos int 之前发生

推荐答案

删除分号，

DELIMITER $$

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

 ...
END$$
DELIMITER ;

这篇关于MySQL BEFORE UPDATE TRIGGER给出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！