When you insert a new record, create a trigger for SQLITE to delete old records.

CREATE TRIGGER [TRIGGER_NAME] AFTER INSERT ON my_table
BEGIN
 DELETE FROM LOG WHERE DATE(CREATE_DATE)> DATE('now','-10 days');
END

CREATE TRIGGER [TRIGGER_NAME] AFTER INSERT ON my_table
BEGIN
 DELETE FROM LOG WHERE ROW_NO NOT IN 
 (SELECT TOP 1000 ROW_NO FROM LOG ORDER BY CREATE_DATE DESC);
END

I have the “log” table in the SQLite database, where I write all the log files.

But I want to pretend The database is getting bigger and bigger-the smartest way to do this is to use the trigger of the insert command-at least I think so…

When a new record is inserted, the trigger will be triggered and delete more than 10 days There are records.

Either…

When inserting a new record, the trigger will be triggered to delete all old records exceeding a certain number (for example, 1000).

I need a sample code.

Kind regards, and thx.

This will create an insert trigger, which will Delete any content whose creation date is more than 10 days.

CREATE TRIGGER [TRIGGER_NAME] AFTER INSERT ON my_table
BEGIN
 DELETE FROM LOG WHERE DATE (CREATE_DATE)> DATE('now','-10 days');
END

If you want to do something based on size like you said 1000 rows, you can do it like this Thing.

CREATE TRIGGER [TRIGGER_NAME] AFTER INSERT ON my_table
BEGIN
 DELETE FROM LOG WHERE ROW_NO NOT IN 
 (SELECT TOP 1000 ROW_NO FROM LOG ORDER BY CREATE_DATE DESC);
END

This will select the 1000 most recent rows and delete anything that is not in the select statement.