Create a event inside a procedure



  • Using Mariadb 10.5.13 is it possible to create a event inside a procedure ?

    The reason is because I would like to have a procedure I can manually trigger, and then run a one time event 3-4 hours later.

    I don't know if I'm failing because of syntax error or because this is just not allowed

    If possible how would you create a procedure that just sleep for 60s, then create a event to run again in 4 hours and sleep for 80s (just need to learn the syntax, the queries inside doesn't matter much)



  • You cannot create an event procedure within stored procedure.

    The reason is because I would like to have a procedure I can manually trigger, and then run a one time event 3-4 hours later.

    This is simple enough. Do you have any service table in your database(s) or in any service or system database? if not then create it, like

    CREATE TABLE serviceDB.serviceTable (
        domainName VARCHAR(255),
        parameterName VARCHAR(255),
        parameterValue LONGBLOB,
        PRIMARY KEY (domainName, parameterName)
    );
    

    Now you create a row like

    INSERT INTO serviceDB.serviceTable (domainName, parameterName, parameterValue)
    VALUES ('myApplication', 'executeMyEventProcedure', NOW() + INTERVAL 4 HOURS);
    

    The event procedure checks this parameter value and performs the action if the execution is set:

    CREATE EVENT myEventProcedure
    ON SCHEDULE EVERY 1 MINUTE
    DO
    BEGIN
        IF EXISTS ( SELECT NULL       -- check does we need to perform the proc action 
                    FROM serviceDB.serviceTable 
                    WHERE domainName = 'myApplication'
                      AND parameterName = 'executeMyEventProcedure'
                      AND parameterValue 

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2