35. 触发器 TRIGGER

本节目标：学完本章你能说出触发器是什么、会写一个简单的审计/校验触发器，并理解 NEW 与 OLD 这两个特殊引用。

触发器（TRIGGER） 是数据库里的一种”自动响应机制”：你事先定义好”当某张表发生 INSERT、UPDATE 或 DELETE 时，自动执行一段 SQL”。它像给表装了个监听器，事件发生就触发，无需你在程序代码里手动去调。在 serverless 的 SQLite 里，触发器定义就存在那个数据库文件里，跟着库走。

一、典型用途

审计日志：有人改了重要数据，自动把”谁、什么时候、改了什么”记到另一张日志表。
数据校验：插入前检查邮箱格式，不合法就拒绝写入。
自动维护派生数据：比如订单金额变了，自动更新用户的累计消费汇总。
自动维护时间戳：比如规定 users 表任何更新都要自动刷新 updated_at 字段，不用业务代码每次都记得写。用 AFTER UPDATE 触发器配合 datetime('now') 即可，调用方完全不用操心”最后修改时间”对不对。
级联软删除：真正的物理删除很危险，常见做法是加一个 deleted_at TEXT 字段做”软删除”。当删除用户时，用触发器把关联的订单也一并标记软删除，而物理行不删，方便日后恢复与审计。

二、创建触发器

基本语法（注意 BEGIN … END 包裹动作体）：

CREATE TRIGGER [IF NOT EXISTS] trigger_name
  [BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE]
  ON table_name
  [WHEN 条件]
BEGIN
  -- 要自动执行的 SQL 语句
END;

这里几个关键点：

时机：BEFORE（事件发生前执行）、AFTER（事件发生后执行）、INSTEAD OF（只用于视图，替代原操作）。
事件：INSERT、UPDATE 或 DELETE。UPDATE 还可以写成 UPDATE OF 列名 只监听特定列。
FOR EACH ROW：SQLite 只支持”行级触发器”，即影响几行就触发几次。这个修饰词可写可不写（默认就是行级）。SQLite 不支持语句级触发器。
WHEN 条件：只有条件为真才触发；省略则每行都触发。

在触发器体内，可以用 NEW.列名 和 OLD.列名 访问正在被插入/更新/删除的那一行：INSERT 时只有 NEW 可用，DELETE 时只有 OLD 可用，UPDATE 时 NEW 和 OLD 都有。

三、实例：用触发器做审计

沿用统一示例表。先建一张日志表，注意日期我们用 created_at TEXT（SQLite 没有专门的日期类型，用 TEXT 存 ISO8601 文本）：

CREATE TABLE order_logs(
  id INTEGER PRIMARY KEY,
  order_id INTEGER,
  action TEXT,
  created_at TEXT
);

CREATE TRIGGER log_order_after_insert
  AFTER INSERT ON orders
BEGIN
  INSERT INTO order_logs(order_id, action, created_at)
  VALUES (NEW.id, 'INSERT', datetime('now'));
END;

现在往 orders 插一条，触发器会自动往 order_logs 写一条记录：

sqlite> INSERT INTO orders(user_id, amount, created_at) VALUES (1, 99.5, '2026-07-31');
sqlite> SELECT * FROM order_logs;
1|1|INSERT|2026-07-31 12:00:00

再看一个校验场景——插入前检查金额必须为正，否则用 RAISE() 中止：

CREATE TRIGGER check_amount_before_insert
  BEFORE INSERT ON orders
  WHEN NEW.amount <= 0
BEGIN
  SELECT RAISE(ABORT, '金额必须为正数');
END;

三（续）：自动维护 updated_at 时间戳

给 users 加一个 updated_at 列，然后用触发器在每行被更新后自动刷新它。再次强调，日期时间仍用 TEXT 存 ISO8601 文本——SQLite 没有原生的日期类型，所谓的”时间”是 datetime() 这类函数算出来的（详见第 38 章）：

ALTER TABLE users ADD COLUMN updated_at TEXT;

CREATE TRIGGER set_user_updated_at
  AFTER UPDATE ON users
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

现在无论你 UPDATE users SET age = 31 WHERE id = 1 改了哪个列，触发器都会把这一行的 updated_at 改写成当前时间。这样”最后修改时间”永远准确，不必在每条 UPDATE 语句里手动拼 updated_at = ...，也避免有人忘记写导致字段过期。

Tip

实用技巧：用这种”更新时自动打时间戳”的触发器，比在应用层逐条拼时间省心且更可靠。它属于”数据库自己该守的规矩”，很适合下沉到触发器里。

四、UPDATE 触发器：OLD 与 NEW 如何配合

UPDATE 事件的特殊之处在于，同一行既有”改之前的值”也有”改之后的值”，分别用 OLD.列名 和 NEW.列名 取到。下面这个 AFTER UPDATE 触发器，只有当邮箱或电话真正变化时才记一笔日志：

CREATE TABLE user_logs(
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  changed_at TEXT
);

CREATE TRIGGER log_user_update
  AFTER UPDATE ON users
  WHEN OLD.email <> NEW.email OR OLD.age <> NEW.age
BEGIN
  INSERT INTO user_logs(user_id, changed_at)
  VALUES (NEW.id, datetime('now'));
END;

注意 WHEN 里的判断：如果只改了别的列（比如 age 没变、email 没变），触发器根本不会记日志，避免产生无意义噪声。

四（续）：自动级联与软删除

外键本可以帮我们做级联删除（删用户时自动删其订单），但有两点务必记牢（见红线）：SQLite 外键默认关闭，必须执行 PRAGMA foreign_keys = ON 且每次新连接都要重开；而且即便开了，外键级联也要显式写 ON DELETE CASCADE。下面这条触发器演示了用”软删除”思路，在删除用户时把其订单标记 deleted_at，而不是物理删除——这样数据可恢复、可审计：

Warning

常见坑：如果你指望触发器或外键帮你维护参照完整性，务必先开 PRAGMA foreign_keys = ON！CLI 里每次新连接默认是关闭的，不显式打开，外键约束和 ON DELETE CASCADE 都不会生效。触发器的自动写入同样不帮你绕过这条规则——它只是”自动执行 SQL”，并不会替你打开外键。

ALTER TABLE orders ADD COLUMN deleted_at TEXT;

CREATE TRIGGER soft_delete_orders_on_user_delete
  AFTER DELETE ON users
BEGIN
  UPDATE orders SET deleted_at = datetime('now')
  WHERE user_id = OLD.id AND deleted_at IS NULL;
END;

删掉某个用户后，他名下的订单不会被物理删除，而是被打上 deleted_at 时间戳。查询时只要加 WHERE deleted_at IS NULL 就能只看”未删除”的数据。相比硬删除，软删除配合触发器更安全、更便于追溯，是生产环境常用的稳妥做法。

五、RAISE 的报错级别与 INSTEAD OF 触发器

RAISE() 不只是 ABORT，它有四种级别，语义不同：

RAISE(ABORT, msg)：中止当前语句，已做的改动回滚到语句开始前，但不回滚整个事务。
RAISE(ROLLBACK, msg)：中止并回滚整个当前事务。
RAISE(FAIL, msg)：当前语句失败，但已执行的改动保留（不回滚）。
RAISE(IGNORE, msg)：跳过当前触发器的剩余动作，不报错。

对视图来说，由于视图只读，你想让它”可写”，就得创建 INSTEAD OF INSERT/UPDATE/DELETE 触发器，把对视图的操作翻译成对基表的实际写入。例如视图 v_user_spend 上建 INSTEAD OF INSERT 触发器，决定真正往哪张基表插数据。这是让视图具备写入能力的唯一官方途径。

Note

重点提示：RAISE(ABORT, 错误信息) 是触发器里专用的报错函数，能在数据不合法时中止当前操作。触发器引用的表必须和触发器在同一数据库文件中，且写表名时直接用表名，不要写 库名.表名。

六、查看与删除

sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
sqlite> DROP TRIGGER IF EXISTS log_order_after_insert;

注意：当你 DROP 一张表时，挂在它上面的所有触发器也会跟着被自动删除。

Tip

实用技巧：触发器把”规则”下沉到数据库层，比在应用代码里到处写校验更集中、更不容易漏。适合做审计日志和强一致性约束，但别把太复杂的业务逻辑全塞进触发器，否则排错困难。

Warning

常见坑：① 触发器是”静默”执行的，调用方往往不知道背后发生了什么，滥用会让数据变更难以追踪。② UPDATE 触发器的 WHEN 要判断 OLD.列 <> NEW.列，否则任何无关列的更新也会记日志，产生大量噪声。③ 别忘了 NEW/OLD 的可用性随事件而变：DELETE 时没有 NEW，INSERT 时没有 OLD，写错会报错。

七、触发器的递归与限制

触发器体里执行的 SQL 也可能再次触发别的触发器，这叫触发器递归（recursive trigger）。默认情况下 SQLite 会阻止递归触发（避免无限循环），只有显式打开 PRAGMA recursive_triggers = ON 才允许。绝大多数业务不需要递归，保持默认即可，否则很容易写出互相调用、无限循环的触发器，把一次简单更新放大成灾难。

还需要知道几个硬限制：

触发器体里不能直接用 SELECT 给调用方返回结果集，只能通过 INSERT/UPDATE/DELETE 或 RAISE() 产生副作用。
触发器只能挂在一张表或视图上，且被改的表必须和触发器在同一个数据库文件里（写表名用裸表名，别写 库名.表名）。
SQLite 只支持行级触发器（FOR EACH ROW），不支持语句级触发器；一次影响多少行就触发多少次。
触发器是静默运行的，调用方往往无感知，所以逻辑要写得清晰可查，复杂业务逻辑别全塞进来。

Note

重点提示：触发器适合做”数据库自己该守的规矩”——审计、时间戳、校验、级联。凡是能放在应用层、需要被肉眼 debug 的业务逻辑，优先放应用层；触发器只作为兜底的一致性与安全网，别让它变成谁也看不懂的”暗箱”。

类比小结：触发器像门的自动感应器——有人进门（INSERT）就自动开灯（写日志），你不用每次手动去按开关；但它默默工作，装太多、规则太绕，反而让你搞不清灯为什么亮了。