CODEWITHSUNDEEP
mysqlsqlsyntaxtriggers
Gleeb
Gleeb

Reputation: 11289

Mysql Trigger to delete all rows older then timestamp

I am having some syntax error in my Trigger.

I am trying to check after each insert if the table is bigger than 5000 rows and if so, delete all rows older than 10 min in 1000 chunks stopping when the table reaches under 1000 rows.

here is the code 

CREATE trigger my_trigger after insert ON myTable 
    IF (SELECT count(Uuid) from myTable) > 5000
    THEN
        WHILE (SELECT count(Uuid) from myTable) > 1000
            DELETE from myTable
                where myTable.created < (NOW() - INTERVAL 10 MINUTE)
        END WHILE
    END IF;

how can I delete all rows older then timestamp?

Upvotes: 0

Views: 3556

Answers (1)

RandomSeed
RandomSeed

Reputation: 29769

The syntax error comes from your invalid WHILE clause. A correct form could be:

SELECT COUNT(Uuid) INTO @cnt FROM myTable;
WHILE (@cnt > 1000)
...

However, this could lead to an (almost) infinite loop, if there are more than 1000 records younger than 10 minutes (sure, eventually all records will be older than 10 minutes :).

But the major issue here is that (reference):

A (...) trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the (...) trigger.

However you can achieve a similar effect with an EVENT (that would purge your table every now and then).

Upvotes: 1

Related Questions