SQLite Forum

can we use a for/while loop inside sqlite triggers ?
Yes, exactly.  

Note that the `select ts, sum(size_t) over (order by ts desc from mqtt6` will output the ts and sum so far obtained in descending order by ts (and ascending sum).  This is a subquery with a limit 1 and a condition on the sum(size_t) so the summing will run until it hits the first row (going backwards in time) where the size_t sum exceeds 1024000 and will return the ts for that row so the delete can delete all the rows less or equal to this ts.

The index on (ts, size_t) serves two purposes.  (1) it can be used by the sum ... over process so that only the index is scanned without need to access or process the underlying table.  (2) to find all the rows to be deleted without scanning the underlying table (thus rows can be inserted out of order if one wants and it will make no difference to the running time).

You can add an order by to the sum ... over query for the greater certainty, but at the moment it is not required (I suppose this is an implementation detail), that is:

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