Limiting SQL Table to only N number of record

Question

I have one SQLite table. I want it to store only 1000 rows maximum.

If a new record comes aftre that limit, then it should automatically delete the last row before inserting the new one.

Is there any way to achieve this?

can you show your table structure? and show what you've tried so far? — Sankar, Jul 10 '16 at 10:10
It consist of 3 columns - Id NUMBER(Primary Key), created_at TIMESTAMP and contents TEXT — shivaji bhosale, Jul 10 '16 at 10:12
You could use a **trigger**: http://www.tutorialspoint.com/sqlite/sqlite_triggers.htm — Phantômaxx, Jul 10 '16 at 10:34

score 0 · Accepted Answer · edited Jul 10 '16 at 10:33

0

Check before you insert

Your command should be like this...

if ((select count(*) from yourTable)=1000)
   delete from yourTable where id= (select max(id) from yourTable)
   insert into yourTable values(@values...)
else
   insert into yourTable values(@values...)

edited Jul 10 '16 at 10:33

Phantômaxx

37,901
21
84
115

answered Jul 10 '16 at 10:16

Sankar

6,908
2
30
53

Limiting SQL Table to only N number of record

1 Answers1