I wanted to make rotation inside my logs table to store last 100 rows only, and decided to use triggers, as in
create trigger rotate_log after insert on logs for each row begin declare cnt int; set cnt = (select count(*) from logs); if cnt >100 then delete from logs limit 1; end if; end
But it doesn't works because of deadlock caused by insert statement. So how to rewrite this with less work?
I am using mysql Ver 15.1 Distrib 10.1.35-MariaDB, for FreeBSD11.2