Noob question on SQL and sqlite triggers

Question

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?

Answer 1

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.

Answer 2

Maybe a different syntactical approach?

CREATE TRIGGER testtrigger ON Table1

FOR INSERT 
AS
BEGIN

  DECLARE @timeNum int

  SET @timeNum = SELECT count(*) FROM Table1 WHERE time > (New.time - 120)

  IF @timeNum > 3

    BEGIN
        INSERT INTO Table2 (time, data) VALUES 
            (NEW.time, 'data1');
    END

END

But also, try some debugging statements. When I was debugging my last trigger for a webservice I put some INSERT statements into a debugging table that I setup. So then you could output the @timeNum every time the trigger gets called, and then put another debug INSERT inside the loop to make see if you actually get into your Table2 INSERT logic.

UPDATE: Sorry! Looks like SqlLite kinda sucks, I did not know that it lacked some of this syntax. Nonetheless, if you are not getting any answers, consider some debugging statements to make sure that your code paths are being called under the right conditions.

Answer 3

Your WHEN clause in the trigger should be a comparison expression which returns true or false, instead of returning a number. Try dlamblin's idea.