title: JDBC, SQLite and triggers: weird behaviour
body: <p>I'm trying to observe changes in SQLite database with help of triggers. My code works as expected with Android-SQLite (via Robolectric) but shows weird behaviour with JDBC. Here are my logs with some expectations explained:</p>
  <pre class="lang-sql prettyprint-override"><code>setAutoCommit(false) -- this transaction sets up a temporary table and triggers
  Statement.execute(CREATE TEMP TABLE IF NOT EXISTS &quot;users_yub3fdo5090g_changes&quot; (&quot;id&quot; INTEGER NOT NULL PRIMARY KEY, &quot;what&quot; INTEGER NOT NULL, &quot;ch0&quot; INTEGER NOT NULL);)
  -- the following trigger is gonna set (primary_key, +1, …) into changes table after INSERT
  Statement.execute(CREATE TEMP TRIGGER &quot;users_yub3fdo5090g_changes_INS&quot; AFTER INSERT ON &quot;users&quot;
  BEGIN
  REPLACE INTO &quot;users_yub3fdo5090g_changes&quot; VALUES (new.&quot;_id&quot;,COALESCE((SELECT &quot;what&quot; FROM &quot;users_yub3fdo5090g_changes&quot; WHERE &quot;id&quot;=new.&quot;_id&quot;),0)+1,-1);
  END)
  -- the following trigger is gonna set (primary_key, 0, …) into changes table after UPDATE
  Statement.execute(CREATE TEMP TRIGGER &quot;users_yub3fdo5090g_changes_UPD&quot; AFTER UPDATE ON &quot;users&quot;
  BEGIN
  REPLACE INTO &quot;users_yub3fdo5090g_changes&quot; VALUES (new.&quot;_id&quot;,0,(CASE WHEN old.&quot;name&quot;=new.&quot;name&quot; THEN 0 ELSE 1 END)|(CASE WHEN old.&quot;email&quot;=new.&quot;email&quot; THEN 0 ELSE 2 END)|COALESCE((SELECT &quot;ch0&quot; FROM &quot;users_yub3fdo5090g_changes&quot; WHERE &quot;id&quot;=new.&quot;_id&quot;),0));
  END)
  Statement.close()
  commit
  
  setAutoCommit(false) -- let's execute INSERT and see what happens
  PreparedStatement.executeUpdate(INSERT INTO &quot;users&quot; (&quot;name&quot;, &quot;email&quot;) VALUES (?, ?);)(A, b)
  commit
  
  setAutoCommit(false)
  Statement.executeQuery(SELECT * FROM &quot;users_yub3fdo5090g_changes&quot;)
  id=1, what=+1 -- everything is good!
  Statement.execute(DELETE FROM &quot;users_yub3fdo5090g_changes&quot;)
  Statement.close()
  commit
  
  setAutoCommit(false) -- let's execute UPDATE and see what happens
  PreparedStatement.executeUpdate(UPDATE &quot;users&quot; SET &quot;name&quot; = ? WHERE &quot;_id&quot; = ?;)(X, 1)
  commit
  
  setAutoCommit(false)
  Statement.executeQuery(SELECT * FROM &quot;users_yub3fdo5090g_changes&quot;)
  id=1, what=+1 -- getting what=+1, what=0 expected!
  Statement.execute(DELETE FROM &quot;users_yub3fdo5090g_changes&quot;)
  Statement.close()
  commit
  </code></pre>
  <p>If I do two updates instead of one, same thing happens. If I do no updates (empty transaction) or delete, nothing happens, as expected.</p>