title: SQLite trigger to update a column on editing updates the column for all records in table
body: <p>Hypothetical table x, with columns (all of type <code>text</code>):</p>
  
  <ul>
  <li>name</li>
  <li>sname</li>
  <li>sqlmodded</li>
  </ul>
  
  <p>I'd like to create a trigger to set the value of <code>sqlmodded</code> to '1' if any column in the record is updated.</p>
  
  <p>My trigger is as follows:</p>
  
  <pre><code>CREATE TRIGGER trigger1
  AFTER UPDATE
  ON x
  FOR EACH ROW
        WHEN old.sqlmodded IS NULL
  BEGIN
      UPDATE x
         SET sqlmodded = '1';
  END;
  </code></pre>
  
  <p>When I run an update statement to change a value of <code>name</code> or <code>sname</code>, the trigger kicks in but alters <code>sqlmodded</code> for all records in the table.</p>
  
  <p>Here's code to reproduce:</p>
  
  <pre><code>CREATE TABLE "x"(
  "NAME" Text,
  "SNAME" Text,
  "sqlmodded" Text );
  
  CREATE TRIGGER "trigger1"
      AFTER UPDATE
      ON "x"
      FOR EACH ROW
      WHEN old.sqlmodded is null
  BEGIN UPDATE x SET sqlmodded = '1'; END;
  </code></pre>
  
  <p>Now insert a few records:</p>
  
  <pre><code>INSERT INTO x
      (
        name
      , sname
      ) VALUES
      (
        "Joe"
      , "Bloggs"
      )
      ;
  
  INSERT INTO x
  (
    name
  , sname
  ) VALUES
  (
    "Joline"
  , "Bloggs"
  )
  ;
  </code></pre>
  
  <p>Now run an update:</p>
  
  <pre><code>UPDATE x
  SET
    name = "Justine"
  WHERE name = "Joline"
  ;
  </code></pre>
  
  <p>If you view the resulting two records both records will have had <code>sqlmodded</code> set to 1.</p>