我试图通过触发器来观察sqlite数据库中的变化。我的代码在androidsqlite（通过roblectric）上工作正常，但在jdbc上表现出奇怪的行为。以下是我的日志，解释了一些期望：

setAutoCommit(false) -- this transaction sets up a temporary table and triggers
Statement.execute(CREATE TEMP TABLE IF NOT EXISTS "users_yub3fdo5090g_changes" ("id" INTEGER NOT NULL PRIMARY KEY, "what" INTEGER NOT NULL, "ch0" INTEGER NOT NULL);)
-- the following trigger is gonna set (primary_key, +1, …) into changes table after INSERT
Statement.execute(CREATE TEMP TRIGGER "users_yub3fdo5090g_changes_INS" AFTER INSERT ON "users"
BEGIN
REPLACE INTO "users_yub3fdo5090g_changes" VALUES (new."_id",COALESCE((SELECT "what" FROM "users_yub3fdo5090g_changes" WHERE "id"=new."_id"),0)+1,-1);
END)
-- the following trigger is gonna set (primary_key, 0, …) into changes table after UPDATE
Statement.execute(CREATE TEMP TRIGGER "users_yub3fdo5090g_changes_UPD" AFTER UPDATE ON "users"
BEGIN
REPLACE INTO "users_yub3fdo5090g_changes" VALUES (new."_id",0,(CASE WHEN old."name"=new."name" THEN 0 ELSE 1 END)|(CASE WHEN old."email"=new."email" THEN 0 ELSE 2 END)|COALESCE((SELECT "ch0" FROM "users_yub3fdo5090g_changes" WHERE "id"=new."_id"),0));
END)
Statement.close()
commit

setAutoCommit(false) -- let's execute INSERT and see what happens
PreparedStatement.executeUpdate(INSERT INTO "users" ("name", "email") VALUES (?, ?);)(A, b)
commit

setAutoCommit(false)
Statement.executeQuery(SELECT * FROM "users_yub3fdo5090g_changes")
id=1, what=+1 -- everything is good!
Statement.execute(DELETE FROM "users_yub3fdo5090g_changes")
Statement.close()
commit

setAutoCommit(false) -- let's execute UPDATE and see what happens
PreparedStatement.executeUpdate(UPDATE "users" SET "name" = ? WHERE "_id" = ?;)(X, 1)
commit

setAutoCommit(false)
Statement.executeQuery(SELECT * FROM "users_yub3fdo5090g_changes")
id=1, what=+1 -- getting what=+1, what=0 expected!
Statement.execute(DELETE FROM "users_yub3fdo5090g_changes")
Statement.close()
commit

如果我做两次更新而不是一次，同样的事情也会发生。如果我没有更新（空事务）或删除，就不会像预期的那样发生任何事情。