MySQL Trigger on insert on same table not working

Question

After spending last week on SO and many other sites, I have two remaining cases for which I am still unable to correctly write trigger/function etc. This is the first time I am working with triggers on a real project.

Here is a sample Table for this example. I am working on offer 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:
A code + storeid is an offer. There can be only one active offer with same Code+StoreID. Therefore I can not set a composite unique key on Code+Storeid+Status because there is no restriction on inactive offers. I just need to make sure a duplicate active offer does not happen. Currently I am doing it from PHP with 1 transition for checking and another transition for inserting.
I tried to create a trigger before insert but it turns out I can not insert on the same table 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 stored procedure doesnot return messages ) but It is not working, It is always foing into if clause, never into else clause.

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 do I implement check-before-insert on this scenerio from Database?

Case 2
I am trying to create a event trigger which will set status of coupons to Inactive for those coupons whose endson date expires. I tried a tutorial http://www.sitepoint.com/how-to-create-mysql-events/, but couldn't make it work. I am saving entries in database by using FROM_UNIXTIME(:EndsOn) in simple sql prepared statement using PDO, and $Coupon[":EndsOn"] = $dateFactory->today()->addWeeks(4)->getTimestamp(); $dateFactory is object of Carbon library which is an extention of PHP DateTime class.

Can anyone give me code or psudocode example that would work?

Andreas Wederbrand · Accepted Answer · 2015-07-24 10:46:49Z

1

For case 1 you shouldn't do the insert if the duplicate detection doesn't find any duplicates, the 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 would work

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

Make sure to have an index on EndsOn and this will be quick.

edited Jul 24, 2015 at 10:46

answered Jul 20, 2015 at 8:42

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abhinav Kulshreshtha Over a year ago

Thanks for the case 1, It worked great. Still doing some more tests on it. Case 2 solution doesn't seem to be working. I set the date for tomorrow. then changed system date to tomorrow. It didn't have any effect. Event sheduler is active. I did change EndsOn > NOW() to EndsOn < NOW(). What could have I overlooked?

Collectives™ on Stack Overflow

MySQL Trigger on insert on same table not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related