Search code examples
mysqlmysql-error-1064

MaxRow Trigger not working in mysql

I wanted to write a trigger which deletes the oldest DB entry if a new one is inserted and the rowcount is larger than 3600 rows. Unfortunately, there is a error(1064) in line 7, but I don't know how to fix it. The column time if defined by using DATETIME in mysql.

CREATE TRIGGER maxRows BEFORE INSERT ON table1 
FOR EACH ROW
BEGIN
    IF ((SELECT COUNT(*) FROM table1) = 3600) THEN
        DELETE FROM table1
        ORDER BY time ASC
        LIMIT 1;
    END IF;    
END;
Solution

  • As you have some DML sentences inside your trigger, try changing the delimiters

    DELIMITER $$

CREATE TRIGGER maxRows BEFORE INSERT ON table1 
FOR EACH ROW
BEGIN
    IF ((SELECT COUNT(*) FROM table1) = 3600) THEN
        DELETE FROM table1
        ORDER BY time ASC
        LIMIT 1;
    END IF;    
END;
END $$

DELIMITER ;