title: SQLite3 update trigger fires on all rows, ignores WHEN and/or WHERE condition(s)
body: <p>I tried to add an UPDATE trigger to a table in Sqlite3 and the trigger updates ALL rows despite my efforts to limit it to only the rows that actually change. Simple made-up example table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS prices (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      base REAL DEFAULT 100.0,
      discount REAL DEFAULT 0.0,
      actual REAL DEFAULT 0.0
  );
  INSERT INTO prices(base,discount,actual) VALUES (100,0,100),(200,0,200),(300,0,300);
  </code></pre>
  
  <p>The actual price is supposed to be updated using the following trigger:</p>
  
  <pre><code>CREATE TRIGGER "upd" AFTER UPDATE OF discount ON prices FOR EACH ROW
  WHEN (OLD.discount &lt;&gt; NEW.discount)
  BEGIN
      UPDATE prices SET actual = base * (1-NEW.discount/100.0)
      WHERE OLD.discount &lt;&gt; NEW.discount;
  END;
  </code></pre>
  
  <p>If I change let's say the 2nd row thus:</p>
  
  <pre><code>UPDATE prices SET discount = 50 WHERE rowid = 2;
  </code></pre>
  
  <p>then the trigger is fired on <strong>all</strong> rows, not only on the second, and the "actual" fields in all rows will be multiplied by 0.5.</p>
  
  <p>As you can see, the trigger wears a belt and a suspender: both the WHEN clause and the WHERE clause try to ensure that only those rows are affected for which the discount value changed. Tried all 4 combinations by specifying either/or WHEN, WHERE, etc. to no avail. Here is the contents of the table after the <code>UPDATE</code>:</p>
  
  <pre><code>1|100.0|0.0|50.0
  2|200.0|50.0|100.0
  3|300.0|0.0|150.0
  </code></pre>
  
  <p>Note that there is a seemingly related <a href="https://stackoverflow.com/questions/23047915/sqlite-update-trigger-changes-all-rows-in-the-table">SO question</a> but there the OP used the following clause: <code>WHERE (OLD.id=NEW.id)</code> which is of course <code>TRUE</code> for all unchanged rows, so no wonder it did not work as expected. I am afraid the solutions offered there do not apply to my case. Any help would be appreciated.</p>
  
  <p>EDIT: After CL. -s explanation here is the correct trigger:</p>
  
  <pre><code>CREATE TRIGGER "upd" AFTER UPDATE OF discount ON prices FOR EACH ROW
  BEGIN
      UPDATE prices SET actual = base * (1-NEW.discount/100.0)
      WHERE id = NEW.id; -- this selects the row that fired the trigger!
  END;
  </code></pre>