Creating Event that execute at end of each month

Posted on

Question :

I need to create an event in mysql that runs at the end of every month 11 PM.I have tried creating an event that creates another events with code below:

DELIMITER |
CREATE EVENT `2014-10-31_CALL_ISRSP` ON SCHEDULE AT '2014-10-31 23:50:00'
DO
BEGIN
call myStoredProcedure();
set @creatNextEvnt := Concat("CREATE EVENT ",LAST_DAY( NOW() + INTERVAL 1 MONTH ),"_CALL_ISRSP ON SCHEDULE AT '",LAST_DAY( NOW() + INTERVAL 1 MONTH )," 23:50:00' DO BEGIN call IncidentsSummaryRecorder(); set @creatNextEvnt := Concat("CREATE EVENT "",LAST_DAY( NOW() + INTERVAL 1 MONTH ),""_CALL_ISRSP ON SCHEDULE AT '"",LAST_DAY( NOW() + INTERVAL 1 MONTH ),"" 23:50:00'"")

Answer :

PREPARE cer FROM @creatNextEvnt

EXECUTE cer

DEALLOCATE PREPARE cer

set @delLastEvent := Concat(""DROP IF EXISTS EVENT "",LAST_DAY(NOW()),""_CALL_ISRSP"")

PREPARE del FROM @delLastEvent

EXECUTE del

Leave a Reply

Your email address will not be published. Required fields are marked *