MySQL trigger on insert on the same table not working
After spending the last week on SO and many other sites, I have two remaining cases for which I still cannot write the trigger / function, etc. correctly. This is the first time I work with triggers in a real project.
Here is a sample table for this example. I am working on suggestion code like scenerio.
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`)
);
Case 1:
Code + storeid is a sentence. There can be only one active offer with the same code + StoreID. Therefore, I cannot set a composite unique key in Code + Storeid + Status, because there is no limit on inactive offers. I just need to make sure no duplicate active offer happens. I am currently doing this from PHP with 1 transition to check and another transition to insert.
I tried to create a trigger before inserting, but it turned out that I cannot insert into the same table the trigger is acting on.
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;
Then I created a function (because the stored procedure doesn't return messages) but it doesn't work, it always inserts in the if clause, never in the 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;
How to implement check-before-insert in this scenerio from database?
Case 2
I am trying to create an event trigger that will set the coupon status to Inactive for those coupons that are due to expire. I tried the tutorial http://www.sitepoint.com/how-to-create-mysql-events/ but couldn't get it to work. I am storing records in a database using FROM_UNIXTIME(:EndsOn)
in a simple prepared sql statement using PDO and $Coupon[":EndsOn"] = $dateFactory->today()->addWeeks(4)->getTimestamp();
$ dateFactory is a Carbon library object which is an extension of the PHP DateTime class.
Can anyone give me some sample code or psudocode that will work?
source to share
For case 1 you shouldn't do an insert if duplicate detection doesn't find duplicates, normal insert will do that
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;
For case 2 I think this will work
CREATE EVENT deleteOld
ON SCHEDULE EVERY HOUR
DO UPDATE demo SET status = 'Inactive' WHERE EndsOn < NOW();
Make sure you have an index on EndsOn and it will be fast.
source to share