Test for COUNT()>X inside SQLite trigger

Question

How can I compare the result of a COUNT inside a trigger in SQLite?

So far, this is the code I've come up with:

CREATE TRIGGER mytrigger
BEFORE INSERT ON mytable
BEGIN
    SELECT CASE WHEN 
        SELECT COUNT (*) FROM mytable >= 3
    THEN
        RAISE(FAIL, "Activated - mytrigger.")
    END;
END;

It fails to compile with:

Error: near "SELECT": syntax error

If I replace SELECT COUNT (*) FROM mytable >= 3 with 1 == 1, it compiles fine, and the trigger executes always.

twoleggedhorse · Accepted Answer · 2013-09-11T09:54:25.277

2

You need to add parenthesis around the whole SELECT statement SELECT COUNT (*) FROM mytable

CREATE TRIGGER mytrigger
BEFORE INSERT ON mytable
BEGIN
    SELECT CASE WHEN 
        (SELECT COUNT (*) FROM mytable) >= 3
    THEN
        RAISE(FAIL, "Activated - mytrigger.")
    END;
END;

edited Sep 11 '13 at 09:54

answered Sep 11 '13 at 09:15

twoleggedhorse

4,938
4
23
38

I thought that SQLite doesn't support `IF..ELSE` statements, after reading this question http://stackoverflow.com/questions/1294619/does-sqlite-supports-any-kind-of-ifcondition-statement-in-the-select – sashoalm Sep 11 '13 at 09:16
I tried it, but it failed with `Error: near "IF": syntax error` – sashoalm Sep 11 '13 at 09:20
Updated answer, I have removed the initial SELECT – twoleggedhorse Sep 11 '13 at 09:22

score 2 · Answer 2 · edited Sep 27 '13 at 10:52

2

Try this code to delete last 50 row from table2 when count is greater than 100

CREATE **TRIGGER IF NOT EXISTS** delete_trigger
BEFORE INSERT ON table1 
  WHEN (SELECT COUNT(*) FROM table1) > 100
    BEGIN 
      delete From table1 where id not in (select id from table1 order by id desc limit 50; 
END;

edited Sep 27 '13 at 10:52

Bridge

29,818
9
60
82

answered Sep 27 '13 at 10:31

Sudhir Patil

31
2

score 0 · Answer 3 · edited Sep 11 '13 at 09:18

0

Try putting the count inside brackets:

CREATE TRIGGER mytrigger
BEFORE INSERT ON mytable
BEGIN
    SELECT CASE WHEN 
        SELECT (COUNT (*) FROM mytable) >= 3
    THEN
        RAISE(FAIL, "Activated - mytrigger.")
    END;
END;

edited Sep 11 '13 at 09:18

sashoalm

75,001
122
434
781

answered Sep 11 '13 at 09:17

mdega

106
6

SQLite, as I've written in the title and in the tags. – sashoalm Sep 11 '13 at 09:27
Yes, as is written at the end of my question, I tried with `1 == 1`. – sashoalm Sep 11 '13 at 09:36
Also, see this http://stackoverflow.com/a/10834149/492336 - it seems that "PL/SQL syntax doesn't allow for including SQL statements in the IF clause", and also from http://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it it seems that SQLite doesn't support `DECLARE`, instead you have to use in-memory/temporary tables. – sashoalm Sep 11 '13 at 09:39
@twoleggedhorse just found the answer, so no need to try further. – sashoalm Sep 11 '13 at 09:50

Test for COUNT()>X inside SQLite trigger

3 Answers3

Linked