0

In my event I have :

    UPDATE Xdb.shop_product_m2m as m2m
    SET m2m.expired=1 WHERE m2m.expires <= now();

And I tried this in the update trigger for the 'shop_product_m2m' table as :

    SET NEW.modified = NOW();
    IF NEW.expired =1 OR NEW.expired = "1" THEN 
        SET NEW.expired = 1;
    ELSE
        SET NEW.expired = 0;
        SET NEW.expires =ADDDATE(NEW.modified, INTERVAL 15 DAY);

But the test in the line 'IF NEW.expired =1 OR NEW.expired = "1" THEN' fails thoroughly.

WHY AM I FAILING IN THIS ?

Thanks in advance..

EDIT : DDL of table 'shop_product_m2m' for added information of replies 

create table if not exists Xdb.shop_product_m2m(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    created datetime NOT NULL,
    modified datetime NOT NULL,
    expires datetime NOT NULL,
    shop int NOT NULL,
    product int NOT NULL,
    price decimal(13,2),
    instock boolean NOT NULL DEFAULT 1,
    expired boolean NOT NULL DEFAULT 0,
    burgainable boolean NOT NULL DEFAULT 1,
    FOREIGN KEY (shop) REFERENCES shop(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product) REFERENCES product(id) ON DELETE CASCADE ON UPDATE CASCADE
);
edam
  • 910
  • 10
  • 29
  • Ok I have deleted my answer looks like the problem is something else I am unable to replicate it, it would be good if you provide some sameple data along with the expected outcome when the trigger executes on update the table. – Abhik Chakraborty May 28 '14 at 13:28
  • @Abhik It never worked buddy ! It ate my all day out. I solved it some other way (some ad-hoc solution) ! Thanks for your interest in this. I appreciate that ! – edam May 28 '14 at 15:22

0 Answers0