SQLite 3.27

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

CREATE TABLE tablename (
  id INTEGER PRIMARY KEY, 
  name TEXT, 
  inserted_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now'))
);
✓
INSERT INTO tablename (name) VALUES
 ('Alice'), ('Bob'), ('Jack');
✓
SELECT * FROM tablename


id
name
inserted_at


1
Alice
2025-10-01 12:31:02.085

2
Bob
2025-10-01 12:31:02.085

3
Jack
2025-10-01 12:31:02.085


CREATE TRIGGER keep_100_rows AFTER INSERT ON tablename
  WHEN (SELECT COUNT(*) FROM tablename) > 3 
  BEGIN
    DELETE FROM tablename
    WHERE id = (SELECT id FROM tablename ORDER BY inserted_at, id LIMIT 1);
  END;
END;

{}


INSERT INTO tablename (name) VALUES
 ('Jill');
✓
SELECT * FROM tablename


id
name
inserted_at


1
Alice
2025-10-01 12:31:02.085

2
Bob
2025-10-01 12:31:02.085

3
Jack
2025-10-01 12:31:02.085

4
Jill
2025-10-01 12:31:02.087


INSERT INTO tablename (name) VALUES
 ('John'), ('Helen');
✓
SELECT * FROM tablename


id
name
inserted_at


1
Alice
2025-10-01 12:31:02.085

2
Bob
2025-10-01 12:31:02.085

3
Jack
2025-10-01 12:31:02.085

4
Jill
2025-10-01 12:31:02.087

5
John
2025-10-01 12:31:02.087

6
Helen
2025-10-01 12:31:02.087

id	name	inserted_at
1	Alice	2025-10-01 12:31:02.085
2	Bob	2025-10-01 12:31:02.085
3	Jack	2025-10-01 12:31:02.085