For a school assignment we need to keep the number of rows in a MySQL table under 100. The newest rows must stay. I've tried using a trigger but with no success.
CREATE TRIGGER delete_rows
AFTER INSERT ON `check`.log
FOR EACH ROW
IF(SELECT COUNT(*) FROM `check`.log)>100
DELETE FROM `check`.log LIMIT 1;
This resulted in the following error:
ERROR 1442 (HY000): Can't update table 'log' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Then I tried monitoring
incron and let it execute a script. When I open
log.ibd with nano,
incron executes my script, but when I use MySQL to insert something into it, nothing happens.
The incron instruction:
/var/lib/mysql/check/log.ibd IN_ALL_EVENTS /etc/scripts/test
How can I ensure that the table never has more than 100 rows?