title: TRIGGER on UPDATE to INSERT row if count(*) is 0
body: <p>I am trying to create a counter in sqlite3</p>
  
  <p>Column c3 is my counter</p>
  
  <p>What I need is a trigger that will insert a new row if c1 and c2 don't match the update statement.</p>
  
  <p>The setup: </p>
  
  <pre><code>CREATE TABLE t1 (c1,c2,c3);
  INSERT INTO t1 VALUES (1,1,1);
  </code></pre>
  
  <p>My update command:</p>
  
  <pre><code>UPDATE t1 SET c3=(SELECT c3 FROM t1 WHERE c1=1 AND c2=1) + 1 WHERE c1=1 AND c2=1;
  </code></pre>
  
  <p>Here is the trigger, what I got but it isn't working:</p>
  
  <pre><code>CREATE TRIGGER tr1 
  BEFORE UPDATE ON t1 
    WHEN (SELECT COUNT(*) FROM t1 WHERE c1=NEW.c1 AND c2=NEW.c2) &lt; 1 
      BEGIN 
        INSERT INTO t1 (c1,c2,c3) VALUES (NEW.c1, NEW.c2, 0); 
      END;
  </code></pre>
  
  <p>Here are some desperate things I tried:</p>
  
  <pre><code>CREATE TRIGGER tr1 BEFORE UPDATE ON t1 WHEN (SELECT COUNT(*) FROM t1 WHERE c1=1 AND c2=2) &lt; 1 BEGIN INSERT INTO t1 (c1,c2,c3) VALUES (2, 2, 0); END;
  CREATE TRIGGER tr2 BEFORE UPDATE ON t1 WHEN (SELECT COUNT(*) FROM t1 WHERE c1=NEW.c1 AND c2=NEW.c2) = 0 BEGIN INSERT INTO t1 VALUES (2, 2, 0); END;
  CREATE TRIGGER tr3 AFTER UPDATE ON t1 WHEN (SELECT COUNT(*) FROM t1 WHERE c1=NEW.c1 AND c2=NEW.c2) = 0 BEGIN INSERT INTO t1 VALUES (2, 2, 0); END;
  </code></pre>