Creating a conditional SQL trigger in SQLite

sql, sqlite, triggers

Solution

This works because the `WHEN` clause needs an expression:

sqlite> .schema Table1
CREATE TABLE Table1 (time int);
CREATE TRIGGER testtrigger AFTER INSERT ON Table1
WHEN 3<(SELECT Count() FROM Table1 WHERE time>(NEW.time-120))
BEGIN
INSERT INTO Table2 (time, data) VALUES (NEW.time,'data1');
END;

Have you looked at this reference page? From what I can tell this is a "misuse of aggregate" which probably stems from statement in the `When` section. You had this:

sqlite> .tables
Table1  Table2
sqlite> .schema Table1
CREATE TABLE Table1 (time int);
CREATE TRIGGER testtrigger AFTER
INSERT ON Table1 WHEN 
( 
   SELECT COUNT() AS tCount FROM
   ( 
     SELECT * FROM Table1 WHERE  
       time > (NEW.time - 120)  
   ) WHERE tCount > 3
) 
BEGIN 
   INSERT INTO Table2 (time, data) VALUES 
   (NEW.time, 'data1');  
END;
sqlite> .schema Table2
CREATE TABLE Table2 (time int,data string);
sqlite> insert into Table1 VALUES (5);
SQL error: misuse of aggregate: 
sqlite>

I tried deleting "`WHERE tCount`" to make it into an expression, but then I got a syntax error at the operator.

So instead I switched things about for the solution above.

Problem

I’m trying to write a trigger for sqlite and just running into all kinds of problems. In truth I think my real problem is with my poor background in the sql language. Anyway here goes… I have two tables Table1 and Table2. Table1 has a column named time (which is a 64bit integer time). I need a trigger that watches for a new row being inserted in Table1. If there are 3 or more rows in Table1 that have time greater than X (a hard coded value in the below example 120 seconds) I need to insert a new row into Table2. Here is what I have so far (note this does not work) ``` CREATE TRIGGER testtrigger AFTER INSERT ON Table1 WHEN ( SELECT COUNT() AS tCount FROM ( SELECT * FROM Table1 WHERE time > (NEW.time - 120) ) WHERE tCount > 3 ) BEGIN INSERT INTO Table2 (time, data) VALUES (NEW.time, 'data1'); END ``` Any kind souls out there who are better in SQL than I?

Original source