Limit number of records in a table in SQLite

Question

I want to implement in SQLite a table that stores up to 100 records.

The behavior that I am looking for is that the table stores records and when it is full and a new record is added, it deletes the oldest record and writes the new one.

I was reading through the SQLite documentation but I don't find what I am looking for.

Any ideas?

EDIT: Sorry, I forgot to explain something important. Each record has an ID AUTOINCREMENT field. This is how I know which records were written first.

I really can't come up with a solution on how to store only 100 records.

Answer 1

For small tables it is not recommended that you have specified keys anyway, so by default it is indexed on rowid.
Thus rowid defines the order in which the records were added.

For each row added:
SELECT rowid FROM TheTable limit 1;
and delete it!
Simplicity itself.
i.e.

delete from TheTable where rowid in (SELECT rowid FROM TheTable limit 1);

Thereby, for each record added at the front end, you remove the first record at the back end.

For tables which do have one or more indices just ignore them and order using rowid.

delete from TheTable where rowid in (SELECT rowid FROM TheTable order by rowid asc limit 1);

Answering this question allowed me to use this technique to alter my own project, to limit the number of files in a "recently used" file list.

Answer 2

It's bad idea. Better to store all data and get only 100 latest

select * from tblmessage order by datefield DSC limit 100

Good idea with trigger )

    CREATE TRIGGER triggername AFTER INSERT ON tablename
    BEGIN
      delete from tblmessage where 
        datefield =(select min(datefield) from tblmessage ) 
        and (select count(*) from tblmessage )=100;
    END;