在同一张表上的MySQL插入触发器未能工作

Question

在同一张表上的MySQL插入触发器未能工作

3

在上周浏览了SO和许多其他网站之后，我还有两个案例无法正确编写触发器/函数等。这是我第一次在一个真实项目中使用触发器。

以下是本示例中的样例表。我正在处理类似于情景的优惠代码。

CREATE TABLE IF NOT EXISTS `Demo` (
  `id` int(100) unsigned NOT NULL AUTO_INCREMENT,
  `Code` varchar(25) NOT NULL,
  `StoreID` int(100) unsigned NOT NULL,
  `EndsOn` date DEFAULT NULL,
  `Status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  PRIMARY KEY (`id`)

);

案例1：
一个代码（Code）和商店ID（StoreID）的组合是一项优惠。同一Code+StoreID组合只能有一项有效的优惠。因此，我不能在Code+Storeid+Status上设置复合唯一键，因为未激活的优惠没有限制。我只需要确保不会出现重复的有效优惠。目前我正在PHP中进行检查并插入另一个事务。
我试图创建一个插入之前的触发器，但结果表明我无法在触发器作用的同一表格上插入。

CREATE TRIGGER `trig_no_dup` BEFORE INSERT ON `Demo` FOR EACH ROW
    BEGIN
        SET @Counter = (SELECT COUNT(*) FROM `Demo` WHERE `StoreID` = NEW.StoreID AND `Code` = NEW.Code AND `Status` = 'Active');
        IF @Counter = 0 THEN
            INSERT INTO Demo (Code, StoreID) values (NEW.Code,NEW.StoreID); 
        ELSE
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Hello, world! From Else Block';
        END IF;
    END;

然后我创建了一个函数（因为存储过程不返回消息），但它没有起作用，它总是进入 if 语句，从未进入 else 语句。

CREATE FUNCTION `func_no_dup` (l_Code varchar(25), l_StoreID int(100) ) RETURNS varchar(255)
BEGIN    
    SET @Counter = (SELECT COUNT(*) FROM `Demo` WHERE `StoreID` = l_StoreID AND `Code` = l_StoreID AND `Status` = 'Active');
    IF @Counter = 0 THEN
        RETURN 'OFFER DOES NOT EXISTS YET!!!!';
    ELSE
        RETURN 'Offer is already active';
    END IF;
END;

如何在数据库中实现"插入前检查"的功能？

场景2
我正在尝试创建一个事件触发器，用于将优惠券的状态设置为"Inactive"，针对那些到期的优惠券。我尝试了一个教程http://www.sitepoint.com/how-to-create-mysql-events/，但是无法使其工作。我在使用PDO的简单SQL预处理语句时使用FROM_UNIXTIME(:EndsOn)来保存数据库中的条目，并且$Coupon[":EndsOn"] = $dateFactory->today()->addWeeks(4)->getTimestamp(); $dateFactory是Carbon库的对象，它是PHP DateTime类的扩展。

有谁能给我提供代码或伪代码示例，以使其正常工作？

- Abhinav Kulshreshtha

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Andreas Wederbrand · Accepted Answer

对于情况1，如果重复检测没有发现任何重复项，则不应该进行插入，正常的插入操作会执行这个功能。

CREATE TRIGGER `trig_no_dup` BEFORE INSERT ON `Demo` FOR EACH ROW
    BEGIN
        SET @Counter = (SELECT COUNT(*) FROM `Demo` WHERE `StoreID` = NEW.StoreID AND `Code` = NEW.Code AND `Status` = 'Active');
        -- if @Counter is 0 then just don't signal an error
        IF @Counter != 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Hello, world! From Else Block';
        END IF;
    END;

对于案例2，我认为这会起作用。

CREATE EVENT deleteOld 
ON SCHEDULE EVERY HOUR
DO UPDATE demo SET status = 'Inactive' WHERE EndsOn < NOW();

确保在 EndsOn 上建立索引，这样查询将会更快。