title: Creating a conditional SQL trigger in SQLite
body: <p>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…</p>
  
  <p>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.</p>
  
  <p>Here is what I have so far (note this does not work)</p>
  
  <pre><code>CREATE TRIGGER testtrigger AFTER  
  INSERT ON Table1 WHEN 
  ( 
     SELECT COUNT() AS tCount FROM
     ( 
       SELECT * FROM Table1 WHERE  
         time &gt; (NEW.time - 120)  
     ) WHERE tCount &gt; 3
  ) 
  BEGIN 
     INSERT INTO Table2 (time, data) VALUES 
     (NEW.time, 'data1');  
  END
  </code></pre>
  
  <p>Any kind souls out there who are better in SQL than I?</p>