SQLite Forum

can we use a for/while loop inside sqlite triggers ?
How about something like:

create trigger less_den_ten_mb_delete after insert on mqtt6 when (select sum(size_t) from mqtt6) > 1024000
  delete from mqtt6
        where ts <= (
                       select ts 
                         from (
                               select ts, 
                                      sum(size_t) over (order by ts desc) as size
                                 from mqtt6
                        where size >= 1024000 
                        limit 1

and get rid of the delete trigger.  You can probably do away with the when clause since if size >= 1024000 is never met then the delete will do nothing and assuming that you have an covering index on (ts, size_t) it will be very fast.