CODEWITHSUNDEEP
mysqldatabasetriggers
Matthew Vanlandingham
Matthew Vanlandingham

Reputation: 646

SQL creating an event in an after insert trigger

For my database, I have to archive a video at a certain unix timestamp which is stored in the database. So, I thought it would be easy enough to create a trigger which runs after insert into the table, and then create an event to schedule the archiving of the video. I wrote the following SQL statement: 

CREATE TRIGGER `ArchiveVido_onInsert` AFTER INSERT ON `tableName`FOR EACH ROW
    CREATE EVENT archiveVideo
      ON SCHEDULE AT inserted.Time_to_Archive
      DO
        UPDATE MediaPage SET is_archived = 1;

But I get the following error:

#1576 Recursion of EVENT DDL statements is forbidden when body is present

How can I do what I'm attempting? Or am I just missing something? Any help would be greatly appreciated.

Upvotes: 1

Views: 1315

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562981

You can't run statements that cause an implicit commit inside a trigger. That includes any DDL statement that defines or modifies a database object (CREATE EVENT counts).

https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html says:

The trigger cannot use statements that explicitly or implicitly begin or end a transaction...

Instead, you should defining a general EVENT that runs periodically and updates any videos that need to be archived.

CREATE EVENT archiveVideo
  ON SCHEDULE EVERY 1 MINUTE
  DO 
    UPDATE MediaPage SET is_archived = 1 
    WHERE is_archived = 0 AND Time_to_Archive < NOW()
    LIMIT 1000;

Make sure there's an index on (is_archived, Time_to_Archive) so the WHERE clause is optimized to match the rows to update.

I added LIMIT 1000 so it wouldn't try to update too many rows in a single transaction. If there are more than 1000 videos to archive at a given time, they'll have to wait 1 more minute for the event to run again.

Upvotes: 1

Related Questions