complex trigger on SQLite

Question

It's possible create on SQLite a "complex" trigger? for complex i mean that trigger body should provide to count row inside a table, then if count it's greater than a fixed value, delete some rows for satisfy previuos condition

SQLite allows [nothing but UPDATE/INSERT/DELETE/SELECT statements](http://www.sqlite.org/lang_createtrigger.html) in a trigger. It might be possible to use those to do what you want if you describe it properly. — CL., Sep 26 '13 at 09:42
i would my table contain a fixed maximum numbers of row, for example 100. If after a insert, row count it's greater than 100, i would delete some old to fit the limit — giozh, Sep 26 '13 at 13:58

score 0 · Answer 1 · answered Sep 26 '13 at 16:16

0

You should be able to do that using the WHERE clause of the trigger definition, eg WHERE count(*>100. Then in the action part of the trigger, define a DELETE statement with a WHERE clause that identifies which "oldest" entry you want to delete.

answered Sep 26 '13 at 16:16

phaworth

399
2
8

score 0 · Answer 2 · answered Sep 26 '13 at 16:37

Assuming that ID is an autoincrementing column, the following query would find those records with the 100 highest ID values, i.e., those that should not be deleted:

SELECT *
FROM MyTable
ORDER BY ID DESC
LIMIT 100

This allows to write the following trigger:

CREATE TRIGGER DeleteOldestMoreThan100
AFTER INSERT ON MyTable
-- WHEN (SELECT COUNT(*) FROM MyTable) > 100  -- not needed
BEGIN
    DELETE FROM MyTable
    WHERE ID NOT IN (SELECT ID
                     FROM MyTable
                     ORDER BY ID DESC
                     LIMIT 100);
END;

complex trigger on SQLite

2 Answers2

Linked