Limit number of records in a table in SQLite

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.

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;