How to create mysql event inside procedure or trigger?

Question

Recently, I was looking for a solution to the following situation:

I have a mysql table with structure:

CREATE TABLE IF NOT EXISTS `battles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `begindate` datetime NOT NULL, `enddate` datetime NOT NULL, PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 

Each battle resists and ends. Begindate is the date and time of insertion and usually ends after three days.

I would like to create a mysql event that stops the battle (sets active = 0) in the enddate battle. And I would like this event to be created on the insert trigger in the battle table.

There is a related issue with very few answers ( here ).

They advise:

You can do this using a trigger and an event scheduler:

• create a trigger in a table that runs every time you update / insert
• this trigger creates a scheduled event that occurs in the datetime row and updates> the second table

I tried to create such a request, but without success.

 DELIMITER | DROP TRIGGER IF EXISTS battle_create_end| CREATE TRIGGER battle_create_end AFTER INSERT ON battles FOR EACH ROW BEGIN CREATE EVENT IF NOT EXISTS CONCAT('battle_end_',NEW.id) ON SCHEDULE AT NEW.enddate DO UPDATE battles SET battles.active = 0 WHERE battles.id = NEW.id; END| DELIMITER ; 

The error I get is this

1576 - Recursion of EVENT DDL statements is prohibited when a body is present

I tried with different delimiters in each line structure without success.

If someone can help, please let me know.

BR, Ilko

Answer 1

Sorry, brother, but from what I read, what you offer is impossible. I don’t think you can create an event using a trigger. Which is a wreck because it would be good for me too.

However, it would be easier to create an event when a line is created for each battle. Hear is an example of the code I found with a guy explaining how events work.

 <?php // establish database connection and filter incoming data // ... // insert blog post with pending status, get id assigned to post $query = "INSERT INTO blog_posts (id, title, post_text, status) VALUES (NULL, :title, :postText, 'pending')"; $stm = $db->prepare($query); $stm->execute(array(":title" => $title, ":postText" => $text)); $id = $db->lastInsertId(); // is this a future post? if (isset($_POST["schedule"], $_POST["time"])) { $scheduleDate = strtotime($_POST["time"]); $query = "CREATE EVENT publish_:id ON SCHEDULE AT FROM_UNIXTIME(:scheduleDate) DO BEGIN UPDATE blog_posts SET status = 'published' WHERE id = :id; END"; $stm = $db->prepare($query); $stm->execute(array(":id" => $id, ":scheduleDate" => $scheduleDate)); } // this is not a future post, publish now else { $query = "UPDATE blog_posts SET status = 'published' WHERE id = :id"; $stm = $db->prepare($query); $stm->execute(array(":id" => $id)); } 

So basically create an event when you add battle to the table.