DELETE inside INSERT Trigger 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 &gt;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
You cannot alter the same table in trigger.

Use rotate (update the oldest record) instead of add and delete. Insert 100 records into your table (if there is no enought records in it) with their timestamp in far past and use

UPDATE logs
SET field1=value1, field2=value2 -- , ...
ORDER BY record_timestamp ASC
LIMIT 1;


The records count will be always unchanged. To increase it simply add the amount of records you need with timestamp in far past, to decrease - delete the amount of oldest records you do not need.

And you do not need in trigger at all...