mysql setting max number of rows with a trigger

max, mysql, triggers

Solution

You can do that with a trigger:

CREATE TRIGGER handleFiveRows BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM tableName) = 5 THEN
        DELETE FROM tableName
        ORDER BY id 
        LIMIT 1;
    END IF;    
END;

Whenever you have 5 rows, this will delete the row with the smallest id and after your insert you will end up with 5 rows again.

Problem

I want to create a table that should have max 5 rows. So if I add a new row and the table already have 5 rows, the trigger should delete the first row and add the new row. e.g.: ``` my_table id | data --------- 1 | a --------- 2 | b --------- 3 | c --------- 4 | d ``` change to ``` my_table id | data --------- 2 | b --------- 3 | c --------- 4 | d --------- 5 | e ```

Original source