CODEWITHSUNDEEP
mysqltriggers
Abhinav Kulshreshtha
Abhinav Kulshreshtha

Reputation: 2205

MySQL Trigger on insert on same table not working

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?

Upvotes: 3

Views: 588

Answers (1)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

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.

Upvotes: 1

Related Questions