sqlite - Rolling rows in SQL table

Question

I'd like to create an SQL table that has no more than n rows of data. When a new row is inserted, I'd like the oldest row removed to make space for the new one.

Is there a typical way of handling this within SQLite?

Should manage it with some outside (third-party) code?

Answer 1

Expanding on Alex' answer, and assuming you have an incrementing, non-repeating serial column on table t named serial which can be used to determine the relative age of rows:

 CREATE TRIGGER ten_rows_only AFTER INSERT ON t
   BEGIN
     DELETE FROM t WHERE serial <= (SELECT serial FROM t ORDER BY serial DESC LIMIT 10, 1);
   END;

This will do nothing when you have fewer than ten rows, and will DELETE the lowest serial when an INSERT would push you to eleven rows.

UPDATE

Here's a slightly more complicated case, where your table records "age" of row in a column which may contain duplicates, as for example a TIMESTAMP column tracking the insert times.

sqlite> .schema t
CREATE TABLE t (id VARCHAR(1) NOT NULL PRIMARY KEY, ts TIMESTAMP NOT NULL);
CREATE TRIGGER ten_rows_only AFTER INSERT ON t
  BEGIN
    DELETE FROM t WHERE id IN (SELECT id FROM t ORDER BY ts DESC LIMIT 10, -1);
  END;

Here we take for granted that we cannot use id to determine relative age, so we delete everything after the first 10 rows ordered by timestamp. (SQLite imposes an arbitrary order on rows sharing the same ts).