triggers - Sqlite 触发器在唯一约束上奇怪地失败

Question

这是 SQLite 版本 3.16.0 2016-11-04

这是我的测试架构:

PRAGMA foreign_keys = ON;
CREATE TABLE A (a_id integer primary key, x int);
CREATE TABLE B (b_id integer primary key, a_id integer not null references A(a_id) ON DELETE RESTRICT ON UPDATE CASCADE, descr text);
CREATE TABLE C (id integer primary key, dt DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TRIGGER Tb after update on B begin
    replace into C (id) select NEW.b_id;
end;

这是一些测试数据(根据 .dump ):

INSERT INTO "A" VALUES(1, 5000);
INSERT INTO "B" VALUES(1, 1, 'none');

现在，如果我手动更新表中的一行 B ，像这样:

UPDATE B SET descr = "test1" WHERE b_id = 1;

我可以在表 B 中看到一个新行(每次触摸 C 时都会更新) :

1|2017-04-17 21:59:42

我也可以手动“触摸” C与在触发器中完成的方式相同:

replace into C (id) select 1;

正如所料， dt列得到更新。到目前为止一切都很好。
但是突然间，如果我改变 a_id , 引用自 B :

UPDATE A SET a_id = 2 WHERE a_id = 1;

我得到 Error: UNIQUE constraint failed: C.id
据我了解，来自 B.a_id 的外键至 A.a_id更新 B的行。这导致触发器试图触摸 C .然后由于某种原因失败了，尽管它在以前的场景中工作得很好。

为什么会发生这种情况，我该如何解决？

Answer 1

您正在使用 REPLACE command ， 哪一个

is an alias for the "INSERT OR REPLACE" variant of the INSERT command.

trigger documentation说:

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

因此，当外部命令是 UPDATE 时，您的 REPLACE 也会变成 UPDATE。

作为一种解决方法，将 REPLACE 替换为适当的 DELETE 和 INSERT 命令。