12.2 创建触发器 · 波仔的 AI 编程实战课

📄 一句话概括

INSERT 触发器读新行、DELETE 读旧行、UPDATE 新老都能读——三种骨架一样，只差读哪几列。 UPDATE 里最常见的需求是「只记真正变了的字段」， 靠 WHEN（SQLite）或 IF UPDATE(列)（SQL Server）筛。 触发器里不能开事务、不能提交回滚——它已经在调用它那条语句的事务里了，再开就是嵌套。

📝 本章和前面几章的关系

12.1 讲「触发器是什么」，这一节讲三种事件各怎么写——INSERT、UPDATE、DELETE。教材 12.2 分三小节，正好三种一一对应。三种的骨架完全一样，区别只在「能读到哪几列」。 演示里三种都真跑了：新订单自动记账、改库存才记账（改名不记）、删商品先归档。 最后一段讲那个限制：触发器里不能有事务控制。

一、INSERT 触发器：新数据一进来就干活

CREATE TRIGGER trg_order_ins AFTER INSERT ON tb_order
BEGIN
    INSERT INTO tb_log (tbl, act, key_id, memo)
    VALUES ('tb_order', 'INSERT', NEW.id,
            '新订单 ' || NEW.order_no || ' 金额 ' || NEW.total);
END

新订单一插进来，日志就有了。 演示里那条日志记的是 新订单 NO20260601001 金额 2599.0—— 触发器里能读 NEW 的任意列。 真实业务里这一步通常是「扣减库存」： 订单明细插入时， UPDATE tb_goods SET stock = stock - NEW.qty WHERE id = NEW.goods_id。 这也是触发器最经典的用法之一——但也是最容易出问题的地方： 库存不够怎么办？ 触发器里得自己判断，不够就报错回滚。 这属于「业务规则进了数据库」，维护成本要提前想清楚。

二、UPDATE 触发器：只记真正变了的字段

CREATE TRIGGER trg_goods_upd2 AFTER UPDATE ON tb_goods
WHEN OLD.price <> NEW.price OR OLD.stock <> NEW.stock
BEGIN
    INSERT INTO tb_log (tbl, act, key_id, memo)
    VALUES ('tb_goods', 'UPDATE', OLD.id,
            '价格 ' || OLD.price || '->' || NEW.price
            || '，库存 ' || OLD.stock || '->' || NEW.stock);
END

演示里做了个对照： 先改商品名（不关心的字段）——日志 0 条；再改库存——日志 1 条，价格 4999.0->4999.0，库存 30->25。 注意那条日志里价格那半截没变。 这很正常：WHEN 只管「这两列动没动」， 不负责挑出「具体哪一列变了」。要挑，得在触发器体里自己比 OLD 和 NEW。 SQL Server 没有这么方便的 WHEN 来判列，它有自己的 WHEN（判条件，不判列）。 判列要用 IF UPDATE(列名)：

CREATE TRIGGER trg_goods_upd ON tb_goods AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(price) OR UPDATE(stock)
    BEGIN
        INSERT INTO tb_log (tbl, act, key_id, memo)
        SELECT 'tb_goods', 'UPDATE', i.id,
               '价格 ' + CAST(d.price AS VARCHAR(20)) + ' -> '
               + CAST(i.price AS VARCHAR(20))
        FROM inserted i JOIN deleted d ON d.id = i.id;
    END;
END;
GO

IF UPDATE(price) 的意思是「这条语句的 SET 列表里有没有出现 price」，不是「值有没有变」。 这一点差别很实际： SET price = price 也算「有」—— 要判「值真变了」，得自己比 inserted 和 deleted。

三、DELETE 触发器：删之前先归档

CREATE TRIGGER trg_goods_del AFTER DELETE ON tb_goods
BEGIN
    INSERT INTO tb_goods_del (id, gname, price, deleted_at)
    VALUES (OLD.id, OLD.gname, OLD.price, datetime('now', 'localtime'));
END

删掉的那行被完整抄进了归档表。 演示里删了 1003 号罗技鼠标， 归档表里立刻出现一行同样的数据，还带删除时间。 这是「软删除」之外的一种做法： 主表真删，历史留在归档表。 适合日志类、审计类的数据——但要注意，归档表会一直涨，得有清理策略。 DELETE 触发器只读得到 OLD（SQL Server 里就是 deleted）——删都删了，没有「新值」可言。 这也是三种触发器里最简单的一种。

四、三种放在一起看

触发器	能读什么	典型用途
`AFTER INSERT`	新行（SQLite：`NEW`）	扣库存、发通知、记流水
`AFTER UPDATE`	旧行 + 新行（`OLD`／`NEW`）	改价审计、状态变更留痕
`AFTER DELETE`	被删的行（`OLD`）	归档、级联清理

三种骨架一样，只有「能读到哪几列」和「什么时候跑」不同。 写的时候可以直接套：CREATE TRIGGER 名字 AFTER 事件 ON 表 BEGIN 干活的语句 END。 还有一个细节值得注意： UPDATE OF price 这种写法能限定「只有 SET 里出现 price 才触发」， 比在触发器体里判断省事。 演示里第一个价格触发器用的就是这个。

五、一个必须知道的限制：触发器里不能有事务控制

CREATE TRIGGER trg_bad AFTER INSERT ON tb_goods
AS
BEGIN
    BEGIN TRANSACTION;   -- 不允许
    ROLLBACK;            -- 不允许
END;
GO

触发器已经在调用它那条语句的事务里面了。 再开事务就是嵌套， 数据库一律拒绝——它不是「不建议」，是「语法上就不让」。 想「否决」这次操作怎么办？ SQL Server 里 ROLLBACK TRANSACTION 在触发器中是允许的特殊情况， 执行它就把整条语句连自己的改动一起撤了。 但更好的做法是：能用约束就别用触发器。 CHECK 约束、外键、 UNIQUE——这些是声明式的，数据库帮你保证；触发器是命令式的，你得自己写对。 能约束解决的，别用触发器。

六、动手 Demo：INSERT、UPDATE、DELETE 三种触发器各建一个

这一段三种触发器各建一个并真跑：新订单记账、改库存才记账（改名不记）、删商品先归档。 中间那张「日志 0 条 / 1 条」的对照是本篇重点——WHEN 条件挡住了不关心的字段。

import sqlite3

c = sqlite3.connect(u":memory:")
# ... 建六表、装样例数据、建 tb_log ...

# INSERT：新订单记账
c.execute(u"CREATE TRIGGER trg_order_ins AFTER INSERT ON tb_order "
          u"BEGIN INSERT INTO tb_log (tbl, act, key_id, memo) "
          u"VALUES ('tb_order', 'INSERT', NEW.id, NEW.order_no); END")
c.execute(u"INSERT INTO tb_order (order_no, member_id, total, state, created) "
          u"VALUES ('NO20260601001', 2, 2599, '待发货', '2026-06-01')")

# UPDATE：只在价格或库存变了的时候记账
c.execute(u"CREATE TRIGGER trg_goods_upd2 AFTER UPDATE ON tb_goods "
          u"WHEN OLD.price <> NEW.price OR OLD.stock <> NEW.stock "
          u"BEGIN INSERT INTO tb_log (tbl, act, key_id, memo) "
          u"VALUES ('tb_goods', 'UPDATE', OLD.id, OLD.stock || '->' || NEW.stock); END")
c.execute(u"UPDATE tb_goods SET gname = '联想笔记本 Pro' WHERE id = 1001")
print(c.execute(u"SELECT COUNT(*) FROM tb_log WHERE act = 'UPDATE'").fetchone())
c.execute(u"UPDATE tb_goods SET stock = 25 WHERE id = 1001")

# DELETE：删之前归档
c.execute(u"CREATE TRIGGER trg_goods_del AFTER DELETE ON tb_goods "
          u"BEGIN INSERT INTO tb_goods_del (id, gname, price) "
          u"VALUES (OLD.id, OLD.gname, OLD.price); END")
c.execute(u"DELETE FROM tb_goods WHERE id = 1003")

实跑输出：

--------------------------------------------------------------------
创建 INSERT、UPDATE、DELETE 三种触发器
--------------------------------------------------------------------


  ① INSERT 触发器：新订单自动扣库存
  --------------------------------------------------------------------

  1> CREATE TRIGGER trg_order_ins AFTER INSERT ON tb_order BEGIN
     INSERT INTO tb_log (tbl, act, key_id, memo)
     VALUES ('tb_order', 'INSERT', NEW.id, '新订单 ' || NEW.order_no || ' 金额 ' ||
         NEW.total); END

  1> INSERT INTO tb_order (order_no, member_id, total, state, created)
     VALUES ('NO20260601001', 2, 2599, '待发货', '2026-06-01')

  1> SELECT tbl, act, key_id, memo FROM tb_log ORDER BY id DESC LIMIT 2
  tbl       act     key_id  memo
  ----------------------------------------------------------
  tb_order  INSERT  6       新订单 NO20260601001 金额 2599.0
  (1 行受影响)
  注意 memo 里拼了两段：订单号和金额。触发器里能读 NEW 的任意列。
  真实业务里这一步往往是「扣减库存」：UPDATE tb_goods SET stock = stock - 1 ...

  ② UPDATE 触发器：只记真正变了的字段
  --------------------------------------------------------------------

  1> CREATE TRIGGER trg_goods_upd2 AFTER
     UPDATE ON tb_goods WHEN OLD.price <> NEW.price OR OLD.stock <> NEW.stock BEGIN
     INSERT INTO tb_log (tbl, act, key_id, memo)
     VALUES ('tb_goods', 'UPDATE', OLD.id, '价格 ' || OLD.price || '->' || NEW.price
         || '，库存 ' || OLD.stock || '->' || NEW.stock); END

  1> UPDATE tb_goods SET gname = '联想笔记本 Pro' WHERE id = 1001
  先改一个「不关心」的字段（商品名），看触发器理不理它：

  1> SELECT COUNT(*) AS 日志条数 FROM tb_log WHERE key_id = 1001 AND act = 'UPDATE'
  日志条数
  --------
  0
  (1 行受影响)

  1> UPDATE tb_goods SET stock = 25 WHERE id = 1001

  1> SELECT memo FROM tb_log WHERE key_id = 1001 AND act = 'UPDATE' ORDER BY id
  memo
  --------------------------------
  价格 4999.0->4999.0，库存 30->25
  (1 行受影响)
  ! 改商品名那次没记事，改库存那次记了——靠的是 WHEN 条件。
  日志里「价格 4999.0->4999.0」这一截没变是正常的：WHEN 只管「这两列动没动」，
  不负责挑出「具体哪一列变了」。要挑，得在触发器体里自己比 OLD 和 NEW。
  SQL Server 里没有 WHEN（其实有，但写法不同），常用 IF UPDATE(列名) 判断，
  或者自己比较 inserted 和 deleted。

  [教材原码 · 本沙箱不执行]
  | -- SQL Server：只关心 price 或 stock 有没有变
  | CREATE TRIGGER trg_goods_upd ON tb_goods AFTER UPDATE
  | AS
  | BEGIN
  |     SET NOCOUNT ON;
  |     IF UPDATE(price) OR UPDATE(stock)
  |     BEGIN
  |         INSERT INTO tb_log (tbl, act, key_id, memo)
  |         SELECT 'tb_goods', 'UPDATE', i.id,
  |                '价格 ' + CAST(d.price AS VARCHAR(20)) + ' -> ' + CAST(i.price AS VARCHAR(20))
  |         FROM inserted i JOIN deleted d ON d.id = i.id;
  |     END;
  | END;
  | GO

  SQL Server 那种写法要自己 JOIN inserted 和 deleted——一个放新值，一个放旧值。

  ③ DELETE 触发器：删之前先归档
  --------------------------------------------------------------------

  1> CREATE TABLE tb_goods_del (id INTEGER, gname TEXT, price REAL, deleted_at TEXT)

  1> CREATE TRIGGER trg_goods_del AFTER DELETE ON tb_goods BEGIN
     INSERT INTO tb_goods_del (id, gname, price, deleted_at)
     VALUES (OLD.id, OLD.gname, OLD.price, datetime('now', 'localtime')); END

  1> DELETE FROM tb_goods WHERE id = 1003

  1> SELECT id, gname, price FROM tb_goods_del
  id    gname     price
  ---------------------
  1003  罗技鼠标  89.9
  (1 行受影响)
  删掉的那行被完整抄进了归档表。DELETE 触发器里能读到 OLD 的每一列。
  这是「软删除」之外的一种做法：主表真删，历史留在归档表。适合日志类、审计类的数据。

  ④ 三种触发器一起看
  --------------------------------------------------------------------
| 触发器 | 能读什么 | 典型用途 |
|---|---|---|
| AFTER INSERT | 新行（SQLite：NEW） | 扣库存、发通知、记流水 |
| AFTER UPDATE | 旧行 + 新行（OLD／NEW） | 改价审计、状态变更留痕 |
| AFTER DELETE | 被删的行（OLD） | 归档、级联清理 |

  三种的骨架一样，区别只在「能读到哪几列」和「什么时候跑」。

  ⑤ 一个必须知道的限制：触发器里不能有事务控制
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE TRIGGER trg_bad AFTER INSERT ON tb_goods
  | AS
  | BEGIN
  |     BEGIN TRANSACTION;   -- 不允许
  |     ROLLBACK;            -- 不允许
  | END;
  | GO

  触发器已经在调用它那条语句的事务里面了，再开事务就是嵌套，数据库一律拒绝。
  要「否决」这次操作，SQL Server 用 ROLLBACK TRANSACTION（这个在触发器里是允许的特殊情况），
  或者干脆不写触发器，改用约束（CHECK、外键）——能约束解决的，别用触发器。

改商品名那一步，日志里 UPDATE 的条数是 0。 这就是 WHEN 的作用—— 它挂在触发器定义上，条件不成立就整个触发器体都不执行。 改库存那一步之后，日志里出现 价格 4999.0->4999.0，库存 30->25。 价格那半截没变是正常的，WHEN 只判「这两列动没动」，不负责挑出具体变了哪一列。 归档表那一行是完整的：id、商品名、价格都在。DELETE 触发器只能读 OLD—— 删都删了，没有新值。 三种触发器写下来会发现骨架完全一样，就三处不同：事件类型、能读 NEW 还是 OLD、条件怎么写。 记住骨架，剩下的是填空题。

30 秒实验

把 WHEN 条件里的 stock 去掉，只留 price，再改一次库存。 这次不记账了——因为价格没变，WHEN 不成立。
把 AFTER UPDATE ON tb_goods 改成 AFTER UPDATE OF stock ON tb_goods，再改商品名。 同样不记账，但这次是「事件级别」挡住的，不是 WHEN。
给归档表也挂一个 INSERT 触发器，往里写东西。 会形成「删商品 → 写归档 → 归档触发器再干别的」的链条——能跑，但别这么设计。

常见误区

「WHEN 能挑出是哪一列变了」。它只能判「条件成不成立」。 要挑具体哪列变了，得在触发器体里自己比 OLD 和 NEW。
「UPDATE(price) 是判断值有没有变」。它判断的是「SET 列表里有没有出现 price」。 SET price = price 也算有。
「触发器里可以开事务，出错了回滚就行」。开不了。 它已经在事务里了，再开就是嵌套，语法上直接拒绝。

自测三问（点开对答案）

1. 三种触发器分别能读到哪些值？ —— **`INSERT` 读新行，`DELETE` 读旧行，`UPDATE` 新老都能读。**SQLite 是 `NEW`／`OLD`， SQL Server 是 `inserted`／`deleted`。 2. 怎么让 `UPDATE` 触发器只在「价格或库存变了」时才跑？ —— **SQLite 用 `WHEN OLD.price <> NEW.price OR OLD.stock <> NEW.stock`；** **SQL Server 用 `IF UPDATE(price) OR UPDATE(stock)`** （注意后者判的是「`SET` 里有没有出现」，不是「值变没变」）。 3. 触发器里为什么不能写 `BEGIN TRANSACTION`？ —— **因为它已经在调用它那条语句的事务里了，再开就是嵌套，数据库直接拒绝。** **要否决操作，SQL Server 用 `ROLLBACK TRANSACTION`（这个允许）。**

下一篇 12.3 讲管理：查看有哪些触发器、ALTER 改定义、DROP 删掉、禁用与启用， 最后量一次「有触发器和没触发器」的批量插入耗时差。

☐ （本篇完，下一篇 12.3 管理触发器）