SQLite how to limit the number of records

user_2647978 0
Viewed 403

I want to limit the number of records in my SQLite table to for example 100 records, and then when I INSERT the 101th record, the first record (the oldest) be removed from the table. In other word, I want to prevent the table from growing more than 100 records and always have the last 100 records. Is there any setting or query with SQLite or should I handle it manually? thanks in advance

count triggers qt sqlite c++
1 Answers

You can do it with a trigger.

Say that your table is this:

CREATE TABLE tablename (
  id INTEGER PRIMARY KEY, 
  name TEXT, 
  inserted_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now'))
);

In the column inserted_at you will have the timestamp of the insertion of each row.
This is not necessary if you declared the column id as:

id INTEGER PRIMARY KEY AUTOINCREMENT

because in this case you could identify the 1st inserted row by the minimum value of the id.

Now create this trigger:

CREATE TRIGGER keep_100_rows AFTER INSERT ON tablename
  WHEN (SELECT COUNT(*) FROM tablename) > 100 
  BEGIN
    DELETE FROM tablename
    WHERE id = (SELECT id FROM tablename ORDER BY inserted_at, id LIMIT 1);
    -- or if you define id as AUTOINCREMENT
    -- WHERE id = (SELECT MIN(id) FROM tablename);
  END;
END;

Every time that you insert a new row, the trigger will check if the table has more than 100 rows and if it does it will delete the 1st inserted row.

See the demo (for max 3 rows).

user_104988280
Related