为何SQLite触发器未按预期仅更新单行反而更新全部行？

你的触发器出现全表更新的原因很直接：UPDATE语句没有限定更新范围。当插入mycolumn为'undefined'的行触发触发器时，原代码里的UPDATE会修改表中所有行的mycolumn字段，导致所有行都被设为默认值。

下面提供两种可行的修正方案：

方案1：修正AFTER INSERT触发器，添加行限定条件 #

在UPDATE语句中加入WHERE id = NEW.id，明确只更新当前插入的那一行：

CREATE TABLE IF NOT EXISTS mytable (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 mycolumn BOOLEAN DEFAULT '0');

CREATE TRIGGER IF NOT EXISTS onnull
AFTER INSERT ON mytable
FOR EACH ROW
WHEN NEW.mycolumn = 'undefined'
BEGIN
    UPDATE mytable
      SET mycolumn = (SELECT dflt_value FROM pragma_table_info('mytable') WHERE name = 'mycolumn' )
      WHERE id = NEW.id; -- 仅更新当前触发触发器的行
END;

INSERT INTO 'mytable' ('mycolumn') VALUES
      (true),
      (false),
      ('undefined');

select * from mytable;

执行后查询结果会符合预期：

1|1
2|0
3|0

方案2：改用BEFORE INSERT触发器（更高效） #

相比插入后再更新，直接在插入前修改待插入的值性能更好，无需额外的UPDATE操作：

CREATE TABLE IF NOT EXISTS mytable (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 mycolumn BOOLEAN DEFAULT '0');

CREATE TRIGGER IF NOT EXISTS onnull
BEFORE INSERT ON mytable
FOR EACH ROW
WHEN NEW.mycolumn = 'undefined'
BEGIN
    SET NEW.mycolumn = (SELECT dflt_value FROM pragma_table_info('mytable') WHERE name = 'mycolumn' );
END;

INSERT INTO 'mytable' ('mycolumn') VALUES
      (true),
      (false),
      ('undefined');

select * from mytable;

这个方案会在数据写入表之前就修正NEW.mycolumn的值，最终插入的就是符合要求的数据，效果和方案1一致，但减少了一次写操作，更推荐使用。

内容的提问来源于stack exchange，提问作者peak