CODEWITHSUNDEEP
mysqlstored-procedures
Cyber
Cyber

Reputation: 2714

How do I create a function / procedure in MariaDB to run an update script when the table got accessed?

I have some records in my DB with a special Status let's say t_status = 'T' and I want to update each t_status which has an t_moddate older than 45 minutes to t_status = 'X'.

How can i put this into a stored procedure in Maria DB?

My table looks like below:

table name: Test columns: t_id,t_status,t_moddate,t_usr_update

I guess my update is like below:

UPDATE TEST SET t_status = 'X' where t_status = 'T' and t_moddate <= now()- interval(45 minutes);

CREATE PROCEDURE myProc()
     BEGIN

       UPDATE TEST
           SET t_status = REPLACE (first_name, 'T', 'X')
         WHERE t_status = 'T' and t_moddate <= now()- interval 45 minute;

     END$$

But how do I get this into a stored procedure to let it run by default?

Upvotes: 0

Views: 217

Answers (1)

Mihai
Mihai

Reputation: 26784

You can use an EVENT run hourly or whatever interval you choose. First enable it

SET GLOBAL event_scheduler = ON;

Then write the event proper

CREATE EVENT somename
  ON SCHEDULE EVERY '1' HOUR
  STARTS '2017-25-12 00:00:00'    
DO 
UPDATE TEST
       SET t_status = REPLACE (first_name, 'T', 'X')
     WHERE t_status = 'T' AND t_moddate <= now()- interval(45 minutes);

Upvotes: 1

Related Questions