一、三样东西定位一个触发器

时机是 BEFORE 或 AFTER，事件是 INSERT／UPDATE／DELETE，再加上「挂在哪张表上」。 这三样一定，一个触发器就唯一确定了；三乘二等于六，一共六种组合。 六种全都能建，而且六种都真的会被触发。 这一点值得亲手验一遍：「建成功」的语法检查很浅，真正跑起来才算数。

六行一模一样，看不出什么名堂，但它把一件事钉死了：触发器不是「一种功能」，是「六个位置」。 选错位置的触发器照样建得成、照样会被触发，只是触发在你不需要的那一刻。

二、最小的触发器：一插就记一条

教材里第一个例子是「插入一条商品，就往日志表写一条」。 FOR EACH ROW 说的是「每影响一行跑一次」。

CREATE TRIGGER trg_goods_ins AFTER INSERT ON tb_g
FOR EACH ROW
BEGIN
    INSERT INTO tb_log(act, ref, note)
    VALUES ('新增', NEW.id, NEW.name);
END;

NEW 指「要写进去的那一行」。NEW.id 就是刚拿到的自增号。 这一步很关键：自增值是在插入过程中才定下来的，除了触发器，别处拿不到。 应用层想知道「刚插进去的是几号」，要么再查一次，要么用 last_insert_id()， 要么就靠触发器顺手记下来。 插入一行 显示器，商品表到 3 行，日志表多 1 条 新增 3 显示器。 注意日志里记的是 3 和 显示器，不是一句「插入成功」——触发器能读到整行数据， 这是它比普通日志强的地方。

三、多条执行语句：BEGIN … END 与 DELIMITER

教材 15.1 第二个小节讲带多条语句的触发器。 写法就是 BEGIN ... END 里排一串，语句之间用分号隔开。

DELIMITER $$
CREATE TRIGGER trg_goods_ins2 AFTER INSERT ON tb_g
FOR EACH ROW
BEGIN
    INSERT INTO tb_log(act, ref, note)
    VALUES ('新增', NEW.id, NEW.name);
    UPDATE tb_stat
       SET total = total + NEW.price * NEW.stock,
           cnt   = cnt   + NEW.stock
     WHERE id = 1;
END $$
DELIMITER ;

DELIMITER 是 mysql 客户端的指令，不是 SQL 语法。 它做的事是「把语句结束符从分号临时改成 $$」， 这样 BEGIN ... END 里的分号就不会被客户端当成语句结尾提前发出去。 所以它在沙箱里报 near "DELIMITER": syntax error， 报得完全正确——解析器当然不认识一个客户端命令。 这条也解释了为什么第 14 章建过程要写它：不是为了数据库，是为了客户端。 沙箱里不需要它，因为 sqlite3 的 execute 一次只送一条语句， BEGIN ... END 整段就是一个字符串，中间的分号不会被切开。 一条 INSERT 同时带动两张表：商品表 3 行、日志表 1 行、汇总表 total 变成 7995。 两条语句绑在一起，一起成功或者一起失败——这才是触发器真正值钱的地方。 靠应用层写两条 SQL 的话，中间断了就只剩一半，汇总表从此对不上。

四、OLD 和 NEW 什么时候有

这是本章第一个真正的坑。OLD 和 NEW 不是什么时候都有。

道理很直白：插入时还没有「旧行」，删除时没有「新行」。 但 SQLite 建的时候不查这个，等到真触发才报错，而且报的是 no such column: OLD.id。

第一行和第三行是「建成功、跑不通」。 这种错误最容易漏：建的时候一句提示都没有，跑业务的时候才突然炸。 更麻烦的是它把 OLD 当成「一个不存在的列」，而不是「你用错了时机」， 所以报错信息不会提示你去改时机。 MySQL 那边建的时候就报 1054，当场能发现。 这一条不是「谁更严格」的问题，而是「什么时候发现」的问题——升级、迁移、换库的时候， 这类「建得成但跑不通」的东西最容易漏网。

五、INSTEAD OF 只给视图

INSTEAD OF 的意思是「不要真的执行，改跑我这段」。 普通表本来写得进去，没什么好「代替」的，所以它只能在视图上用。

两边互不通用：视图只认 INSTEAD OF，普通表只认 BEFORE／AFTER。 这正好补上第 12 章留下的那个口子——视图「一律只读，改不了」， 加了 INSTEAD OF 触发器就能改了。 机制很朴素：往视图里 INSERT 时，数据库不真去改视图，而是把这一行交给触发器； 触发器里自己决定往哪张基表写、写什么。 这一手叫「可更新视图」，本质是把「怎么写」这段逻辑外置了。 MySQL 没有 INSTEAD OF。 它的视图能不能更新，是按「这个视图够不够简单」自动判断的：直接来自一张表、 没聚合没 DISTINCT 没子查询，就允许改；复杂了就直接拒绝。 两边的思路完全不同：一个靠触发器补，一个靠规则判。

六、FOR EACH ROW 不是可选项，是声明

MySQL 语法里 FOR EACH ROW 必须写，因为它只有行级触发器。 SQLite 允许省略，但省略了行为不变：仍然每行跑一次。 不写 FOR EACH ROW 建一条，然后一条语句插三行，日志表里是 3 条。 三行三条，说明它照样逐行跑。 所以这三个词不是「开关」，是「声明」——声明这个触发器的粒度是行。 写不写只影响语法通不通，不影响它是行级还是语句级。 教材里一律写全，是对的：省它没有任何好处，只会让人误以为存在语句级触发器。

七、动手 Demo：六种组合、一个最小触发器、错用 OLD

这一段把 15.1 的四件事一次跑完：六种「时机 × 事件」组合各建一个并真触发一次、 一个最小的日志触发器、带两条语句的 BEGIN ... END，最后把 OLD／NEW 的六种引用挨个试一遍， 看哪几种「建得成、跑不通」。

import sqlite3

c = sqlite3.connect(u":memory:", isolation_level=None)
c.executescript(u"""
CREATE TABLE tb_g (id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT, price REAL, stock INTEGER);
CREATE TABLE tb_log (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     act TEXT, ref INTEGER, note TEXT);
CREATE TABLE tb_stat (id INTEGER PRIMARY KEY, total REAL, cnt INTEGER);
INSERT INTO tb_g(name, price, stock) VALUES ('机械键盘', 399.0, 12);
INSERT INTO tb_g(name, price, stock) VALUES ('无线鼠标', 129.0, 30);
INSERT INTO tb_stat VALUES (1, 0, 0);
""")

# ① 六种组合：建一个、触发一次、看日志涨没涨
for timing in (u"BEFORE", u"AFTER"):
    for ev in (u"INSERT", u"UPDATE", u"DELETE"):
        nm = u"trg_%s_%s" % (timing.lower(), ev.lower())
        c.execute(u"CREATE TRIGGER %s %s %s ON tb_g FOR EACH ROW "
                  u"BEGIN INSERT INTO tb_log(act) VALUES ('%s'); END"
                  % (nm, timing, ev, nm))

# ② 最小触发器：NEW 里能拿到刚生成的自增号
c.execute(u"CREATE TRIGGER trg_goods_ins AFTER INSERT ON tb_g FOR EACH ROW "
          u"BEGIN INSERT INTO tb_log(act, ref, note) "
          u"VALUES ('新增', NEW.id, NEW.name); END")
c.execute(u"INSERT INTO tb_g(name, price, stock) VALUES ('显示器', 1599.0, 5)")
print(c.execute(u"SELECT act, ref, note FROM tb_log").fetchall())

# ③ BEGIN ... END：一条 INSERT 同时带动日志和汇总
c.execute(u"CREATE TRIGGER trg_goods_ins2 AFTER INSERT ON tb_g FOR EACH ROW "
          u"BEGIN "
          u"INSERT INTO tb_log(act, ref, note) VALUES ('新增', NEW.id, NEW.name); "
          u"UPDATE tb_stat SET total = total + NEW.price * NEW.stock, "
          u"cnt = cnt + NEW.stock WHERE id = 1; END")

# ④ OLD / NEW 用错时机：建得成，跑不通
for timing, ev, ref in ((u"AFTER", u"INSERT", u"OLD"),
                        (u"BEFORE", u"DELETE", u"NEW")):
    c.execute(u"CREATE TRIGGER v_%s_%s ON tb_g %s %s FOR EACH ROW "
              u"BEGIN INSERT INTO tb_log(act, ref) VALUES ('x', %s.id); END"
              % (ref.lower(), ev.lower(), timing, ev, ref))
    try:
        c.execute(u"INSERT INTO tb_g(name, price, stock) VALUES ('试', 1.0, 1)")
    except sqlite3.Error as e:
        print(u"建成功，用起来报：", e)

实跑输出：

--------------------------------------------------------------------
创建触发器：把一段动作挂到表上
--------------------------------------------------------------------


  六种「时机 × 事件」组合，全都能建
  --------------------------------------------------------------------
**触发器由三样东西定位自己：时机、事件、挂在哪张表上。**
**时机是 `BEFORE` 或 `AFTER`，事件是 `INSERT`／`UPDATE`／`DELETE`。**
**三样一定就锁死一种组合，三乘二等于六，六种全都能建。**

  时机    事件    建触发器  实际执行一次
  ---------------------------------------
  BEFORE  INSERT  建成功    成功，记 1 条
  BEFORE  UPDATE  建成功    成功，记 1 条
  BEFORE  DELETE  建成功    成功，记 1 条
  AFTER   INSERT  建成功    成功，记 1 条
  AFTER   UPDATE  建成功    成功，记 1 条
  AFTER   DELETE  建成功    成功，记 1 条

**注意最后一列：每种组合都真被触发了一次，日志表里确实多了一条。**
**「建成功」不算数，得看它到底有没有在正确的时刻跑起来。**

  最小的触发器：一插就记一条日志
  --------------------------------------------------------------------
**教材里最简单的例子是「插入一条记录，就往日志表写一条」。**
**`FOR EACH ROW` 说的是「每影响一行跑一次」，这是 MySQL 唯一支持的粒度，SQLite 也一样。**
  CREATE TRIGGER trg_goods_ins AFTER INSERT ON tb_g FOR EACH ROW BEGIN
  INSERT INTO tb_log(act, ref, note)
  VALUES ('新增', NEW.id, NEW.name); END

  mysql> INSERT INTO tb_g(name, price, stock) VALUES ('显示器', 1599.0, 5)
  Query OK

  mysql> SELECT id, name, price, stock FROM tb_g
  id name     price stock
  -----------------------
  1  机械键盘 399   12
  2  无线鼠标 129   30
  3  显示器   1599  5
  3 rows in set

  mysql> SELECT act, ref, note FROM tb_log
  act  ref note
  ---------------
  新增 3   显示器
  1 row in set

**`NEW` 指「要写进去的那一行」，`NEW.id` 就是刚拿到的自增号。**
**这一步很关键：自增值是在插入过程中才定下来的，只有触发器能拿到它。**

  多条执行语句：BEGIN ... END 里排一串
  --------------------------------------------------------------------
**教材 15.1 的第二个小节讲的就是这个：`BEGIN ... END` 里可以放好几条语句。**
**沙箱里也一样，只要用分号隔开、整体包在 BEGIN 和 END 之间。**

  [教材原码 · 本沙箱不执行]
  | DELIMITER $$
  | CREATE TRIGGER trg_goods_ins2 AFTER INSERT ON tb_g
  | FOR EACH ROW
  | BEGIN
  |     INSERT INTO tb_log(act, ref, note) VALUES ('新增', NEW.id, NEW.name);
  |     UPDATE tb_stat SET total = total + NEW.price * NEW.stock,
  |                        cnt   = cnt   + NEW.stock
  |     WHERE id = 1;
  | END $$
  | DELIMITER ;
  DELIMITER 是 mysql 客户端的指令，不是 SQL，所以沙箱报 near "DELIMITER"。

  CREATE TRIGGER trg_goods_ins2 AFTER INSERT ON tb_g FOR EACH ROW BEGIN
  INSERT INTO tb_log(act, ref, note)
  VALUES ('新增', NEW.id, NEW.name);
  UPDATE tb_stat
  SET total = total + NEW.price * NEW.stock, cnt = cnt + NEW.stock
  WHERE id = 1; END
  mysql> 一条 INSERT 同时带动日志表和汇总表：

  表       内容
  --------------------------------
  tb_g     3 行
  tb_log   1 行
  tb_stat  total = 7995.0，cnt = 5

**两条语句绑在一起，一起成功或者一起失败——这才是触发器真正值钱的地方。**
**如果靠应用层写两条 SQL，中间断了就只剩一半，汇总表从此对不上。**

  建得成，用起来才炸：OLD 用错了时机
  --------------------------------------------------------------------
**这是本章第一个真正的坑。`OLD` 和 `NEW` 不是什么时候都有。**
**INSERT 的时候还没有「旧行」，只有 `NEW`；DELETE 的时候没有「新行」，只有 `OLD`。**
**SQLite 建的时候不查这个，等到真触发才报错。**

  时机 + 事件    引用  建的时候  触发的时候
  -----------------------------------------------------
  AFTER INSERT   OLD   成功      no such column: OLD.id
  AFTER INSERT   NEW   成功      成功
  BEFORE DELETE  NEW   成功      no such column: NEW.id
  BEFORE DELETE  OLD   成功      成功
  BEFORE UPDATE  OLD   成功      成功
  BEFORE UPDATE  NEW   成功      成功

**第一行和第三行是「建成功、跑不通」，中间那四行正常。**
**报错是 `no such column: OLD.id`——它把 `OLD` 当成一个不存在的东西，不是当成写错了。**
**这种错误最容易漏：建的时候一句提示都没有，跑业务的时候才突然炸，而且炸在生产环境。**

  INSTEAD OF 是给视图的补丁，普通表用不了
  --------------------------------------------------------------------
**`INSTEAD OF` 的意思是「不要真的执行，改跑我这段」。**
**普通表本来写得进去，没什么好「代替」的，所以只能在视图上用。**
**MySQL 根本没有这个时机，它只有 BEFORE 和 AFTER 两种。**

  对象         时机        结果
  ------------------------------------------------------------------------
  普通表 tb_g  INSTEAD OF  cannot create INSTEAD OF trigger on table: tb_g
  视图 v_g     INSTEAD OF  建成功
  视图 v_g     AFTER       cannot create AFTER trigger on view: v_g

**视图上只认 `INSTEAD OF`，普通表上只认 `BEFORE`／`AFTER`，两边互不通用。**
**建好那个视图触发器，再往视图里插一行，看它是不是真落到了基表：**

  mysql> INSERT INTO v_g(name, price, stock) VALUES ('移动硬盘', 459.0, 8)

  mysql> SELECT id, name, price FROM tb_g
  id name     price
  -----------------
  1  机械键盘 399
  2  无线鼠标 129
  3  移动硬盘 459
  3 rows in set

  FOR EACH ROW 可以不写，但它仍然是行级
  --------------------------------------------------------------------
**MySQL 的语法要求 `FOR EACH ROW` 必须写，因为它只有行级触发器。**
**SQLite 允许省略，但省略了行为不变：还是每行跑一次。**
**所以这不是「语句级触发器」，只是「可以不写这三个词」。**

  不写 FOR EACH ROW 建一条：建成功
  一条语句插三行，日志表里有 3 条

**三行三条，说明它照样逐行跑。**
**写不写只影响语法通不通，不影响它是行级还是语句级——这一点很容易望文生义。**

六种组合那一段，重点不是「都成功」，而是每一行都真被触发了一次。 日志表里确实多了一条，说明时机和事件都对上了——这才叫建对了。 最小触发器那一段，NEW.id 拿到的是 3：前两行是初始化数据，第三行才是刚插的。 这个自增号在插入前不存在、插入后从别处也拿不到（除非再查一次）， 只有触发器能在「刚好」的那一刻读到它。 第四节那两个错误是本篇最值钱的输出：no such column: OLD.id 和 no such column: NEW.id。 注意它报的是「没有这一列」，不是「这个时机不能用」——报错方向指错了， 人容易一路往「列名写错了」上查。

30 秒实验

1. 建一个 BEFORE INSERT 触发器，往日志表写 NEW.name； 再建一个 AFTER INSERT，也写 NEW.name。 两个都插一行，看日志是不是两条、顺序是不是 BEFORE 在前。
2. 故意在 AFTER INSERT 触发器里写 OLD.id，先把触发器建起来（会成功），再插一行，看报错原文。 然后把触发器删掉重建成 BEFORE DELETE 版，看是不是就能跑了。
3. 建一个 INSTEAD OF INSERT 触发器挂在视图上，往视图插一行， 再查基表——确认那一行真的落到了基表里，而且视图本身「什么都没有存」。

常见误区

1. 以为「触发器建成功」就等于「能用」。OLD／NEW 用错时机、引用不存在的表， SQLite 都是建的时候一声不吭、用的时候才炸。建完必须拿一条真数据试一遍。
2. 以为 DELIMITER 是 SQL。它是 mysql 客户端的指令，作用是临时改语句结束符。 别的客户端（Navicat、DBeaver、程序里的驱动）各有各的处理方式，不能照搬。
3. 以为 FOR EACH ROW 可写可不写、写了就是行级、不写就是语句级。MySQL 只有行级触发器， FOR EACH ROW 是必写项；SQLite 允许省略，省略了仍然是行级。

下一篇讲怎么把已经建好的触发器查出来看——SHOW TRIGGERS 在沙箱里不通，得换一张表。

• ☐ （本篇完，下一篇 15.2 查看触发器）

相关笔记

• 创建存储过程和存储函数
• 创建视图
• 定义完整性约束
• 🛢️ MySQL从入门到精通 · 索引