SQLite3 insert trigger

Requirement: The t_VerifyCsmDetail table in the database needs to store a maximum of 10 million records, and delete the oldest record when the number exceeds the limit. Idea: Set the insert trigger. Determine the table zhongji before inserting

Requirement: The t_VerifyCsmDetail table in the database needs to store a maximum of 10 million records, and delete the oldest record when the number exceeds the limit. Idea: Set the insert trigger. Determine the table zhongji before inserting

Requirement: The t_VerifyCsmDetail table in the database needs to store a maximum of 10 million records, and delete the oldest record when the number exceeds the limit.

Idea: Set the insert trigger. Determine the total number of records in the table before insertion. If there are more than 99999 records, delete the oldest record.

The Code is as follows:

Create trigger VRF_insert

Before insert on t_VerifyCsmDetail

For each row

When (select COUNT (*) fromt_VerifyCsmDetail)> 99999)

Begin

Delete from t_VerifyCsmDetail where LocalNO = (select MIN (LocalNO) from t_VerifyCsmDetail );

End

Among them, VRF_insert is the trigger name; before indicates judgment before insertion; for each row can be omitted, please refer to Baidu for specific meanings;

When (select COUNT (*) from t_VerifyCsmDetail)> 1) indicates that the delete record operation is triggered when the total number of records is greater than 99999;

Statement delete from t_VerifyCsmDetail where LocalNO = (select MIN (LocalNO) from t_VerifyCsmDetail,

T_VerifyCsmDetail is the table name, And LocalNO is the field name of one of the fields (in my table, it is set to the auto-incrementing primary key type as int ),

Select MIN (LocalNO) from t_VerifyCsmDetail indicates the minimum value of LocalNO in the table,

Delete from t_VerifyCsmDetail whereLocalNO = xx; delete this record.

NOTE: If there are multiple SQL statements between begin and end, each statement must be separated by a semicolon; end is not followed by a semicolon.

In addition, make sure that sqlite3 has been fully transplanted to linux.

The reason why I failed to set the trigger at the beginning is that the sqlite3 static library libsqlite3.so. 0 and the command file for executing sqlite3 are not placed in/lib and/bin.

Analysis on the combination of storage and data types in SQLite3

SQLite3 installation and basic operations

Simple Application of SQLite databases in Ubuntu 12.04

How to install SQLite in Ubuntu 12.04

Basics of SQLite Database

SQLite details: click here
SQLite: click here

This article permanently updates the link address: