0

As the question implies I need to declare a @var = 1 variable in a MySQL events as : 

DELIMITER $
SET @check_before_update = 1 $
DROP EVENT IF EXISTS expire_product_from_shop $

CREATE 
EVENT IF NOT EXISTS expire_product_from_shop
ON SCHEDULE EVERY 2 MINUTE STARTS  now()
DO BEGIN 

    SET @check_before_update = 0;

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

    SET @check_before_update = 1;           


END $

DELIMITER ;

So that when I use a trigger I can resist an update trigger "before update" as :

DROP TRIGGER IF EXISTS shop_product_m2m_before_update $
CREATE TRIGGER shop_product_m2m_before_update 
BEFORE UPDATE ON shop_product_m2m
FOR EACH ROW
BEGIN
IF @check_before_update != 0 THEN

   SET NEW.modified = NOW();    
   SET NEW.expired = 0;
   SET NEW.expires =ADDDATE(NEW.modified, INTERVAL 15 DAY);


END IF ;


END$

I want a session variable that is set from an event and found when a trigger is being fired because of the event.. As you can notice in the question: I'm trying to run an event and that event causing a before update trigger to be fired ! For which my some other intention is being violated. To prevent that I will need that @check_before_update variable. Thanx

edam
  • 910
  • 10
  • 29
  • You should describe what you want to do in language rather than code. Performing an event every two minutes seems like a bad idea and a waste of server cycles on the database. There are usually better approaches to solving the real problem. – Gordon Linoff May 27 '14 at 13:53
  • @GordonLinoff Yep! I know that sir.. But I was testing and debugging with that code snippet. I want a session variable that is set from an event and found when a trigger is being fired because of the event.. As you can notice in the question: I'm trying to run an event and that event causing a **before update trigger** to be fired ! For which my some other intention is being violated. To prevent that I will need that **@check_before_update** variable. Thanx – edam May 27 '14 at 14:03

0 Answers0