android – Is it possible to limit the number of records in a table?


Is it possible in SQLite to limit the size of a table by the number of records and how to organize it? For example, the table gains a total of 100 rows, after which the oldest records are deleted and new ones are written.


You can write a trigger to insert new records into a table.
Suppose you have a _table with fields

  • _id (some key)
  • _time (time when the entry was created)

Then the trigger will look like this

CREATE TRIGGER delete_till_50 INSERT ON _table WHEN (select count(*) from _table)>100 
    DELETE FROM _table WHERE _table._id IN  (SELECT _table._id FROM _table ORDER BY _table._time  limit (select count(*) - 100 from _table ));

When a new record is inserted, the oldest records will be deleted. There will always be no more than 100 entries in the database.

Scroll to Top