SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select sqlite_version();


sqlite_version()


3.27.2


create table mytable (_id integer primary key, mytimestamp timestamp);
✓
CREATE TRIGGER trg
  AFTER INSERT ON mytable
  FOR EACH ROW
BEGIN 
  DELETE FROM mytable WHERE _id = (SELECT _id FROM mytable ORDER BY mytimestamp DESC LIMIT 1 OFFSET 3);
END;
✓
insert into mytable (mytimestamp)
with cte(myid, mytimestamp) as
(
  select 1, date('2020-05-01')
  union all
  select myid + 1,  date('2020-05-01', '+' || myid || ' days') from cte where myid < 10
)
select mytimestamp from cte;
✓
select * from mytable;


_id
mytimestamp


8
2020-05-08

9
2020-05-09

10
2020-05-10


insert into mytable (mytimestamp) values (date('2020-05-01'));
✓
insert into mytable (mytimestamp) values (date('2020-06-30'));
✓
select * from mytable order by _id;


_id
mytimestamp


9
2020-05-09

10
2020-05-10

11
2020-06-30