Question:
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.
Answer:
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
BEGIN
DELETE FROM _table WHERE _table._id IN (SELECT _table._id FROM _table ORDER BY _table._time limit (select count(*) - 100 from _table ));
END;
When a new record is inserted, the oldest records will be deleted. There will always be no more than 100 entries in the database.