一、触发器：事件一发生就自动执行

三个要素：什么事件（INSERT／UPDATE／DELETE）、什么时机（BEFORE／AFTER）、 做什么动作。 三者凑齐，触发器就能建起来。 它和约束的区别：约束只能看「本行的值」， 触发器能查别的表、写别的表、抛自定义错误。 所以触发器更强，也更危险。 NEW 与 OLD 是两个特殊名字：NEW 指新的一行（INSERT 与 UPDATE 时有）， OLD 指旧的那一行（UPDATE 与 DELETE 时有）。取列值就写 NEW.列名。

二、建触发器：三个真跑的例子

一是 AFTER INSERT 记账：往 orderitems 插一行， 触发器往 audit_log 写一条「谁、哪张单、哪个产品」。 这就是「操作日志」最朴素的实现。 二是 BEFORE DELETE 留底：删之前把整行复制到备份表。 必须用 BEFORE——AFTER 的时候行已经没了，OLD 取不到（多数引擎如此）。 三是 BEFORE INSERT 校验：WHEN NEW.quantity <= 0 时 SELECT RAISE(ABORT, '数量必须大于 0')， 插入直接被中止，还带上你写的错误信息。 注意 RAISE 的三种动作：ABORT 中止当前语句、FAIL 中止但保留已改的、 ROLLBACK 连事务一起撤、IGNORE 跳过这一行。常用的是 ABORT。

三、MySQL 侧的写法与限制（教材原码）

语法：CREATE TRIGGER 名字 AFTER INSERT ON 表 FOR EACH ROW ...，删除用 DROP TRIGGER 名字。 MySQL 只支持行级触发器，一次插一百行就触发一百次。 MySQL 有个硬限制：触发器里不能改「触发它的那张表」， 否则会自己触发自己。 想记日志得写到另一张表里。 MySQL 5.0 起一个表最多六个触发器（BEFORE／AFTER 各三个动作）， 再多就得合并逻辑——这也是「别把触发器当万能」的现实理由。

四、触发器该不该用

好处只有一个，但很硬：不会忘。 谁写数据都会触发， 不依赖应用代码守规矩——多个入口写同一张表时，这个价值很大。 坏处有三个：行为隐式（看那条 INSERT 谁都想不到还改了别的表）； 难调试（报错常指不到触发点）；有性能代价（每次写都多一遍动作，批量插入时是一百遍）。 教材的态度是「能用约束就别用触发器」： NOT NULL、主键、CHECK 更简单、更透明、更快。 触发器留给「约束表达不了」的事， 比如跨表日志、跨表校验。

五、小结

教材小结：触发器按事件与时机绑定动作，用 NEW／OLD 取行，可建可删，MySQL 只有行级且不能改本表。 本课补三条：一是留底要用 BEFORE，AFTER 时 OLD 取不到； 二是 RAISE(ABORT) 能带自定义错误信息；三是能用约束就别用触发器。

六、动手 Demo：记账、留底、拦人，三个触发器

Demo 七段：先建日志表，再建三个触发器分别做记账、留底、校验，然后看清单、删掉一个， 最后讲 MySQL 的写法和该不该用。 注意每段之后表里多出来的行——它们都不是我插的，是触发器写的。

import sqlite3
con = sqlite3.connect(":memory:")

# 一张空的日志表
con.execute("CREATE TABLE audit_log (id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "action TEXT, detail TEXT)")
# AFTER INSERT：插入之后自动记账，NEW 指新行
con.execute("CREATE TRIGGER trg_items_insert AFTER INSERT ON orderitems "
            "BEGIN INSERT INTO audit_log (action, detail) VALUES "
            "('insert', NEW.order_num || '/' || NEW.prod_id); END")
con.execute("INSERT INTO orderitems VALUES (20005, 5, 'SAFE', 1, 50.00)")
con.execute("SELECT * FROM audit_log")
# BEFORE DELETE：删之前先留底，OLD 指被删的行
con.execute("CREATE TABLE items_deleted (order_num INTEGER, prod_id TEXT, "
            "quantity INTEGER, item_price REAL)")
con.execute("CREATE TRIGGER trg_items_delete BEFORE DELETE ON orderitems "
            "BEGIN INSERT INTO items_deleted VALUES "
            "(OLD.order_num, OLD.prod_id, OLD.quantity, OLD.item_price); END")
# BEFORE INSERT + RAISE：不合规就当场中止
con.execute("CREATE TRIGGER trg_qty_check BEFORE INSERT ON orderitems "
            "WHEN NEW.quantity <= 0 "
            "BEGIN SELECT RAISE(ABORT, '数量必须大于 0'); END")
# 看清单、删触发器
con.execute("SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger'")
con.execute("DROP TRIGGER trg_qty_check")

实跑输出：

--------------------------------------------------------------------
第二十五课：使用触发器
--------------------------------------------------------------------

  【一】触发器是什么：事件一发生就自动执行
  SQL> CREATE TABLE audit_log (id INTEGER PRIMARY KEY AUTOINCREMENT, action TEXT, detail TEXT)
  完成
  SQL> SELECT COUNT(*) AS n FROM audit_log
  n
  --
  0
  （1 行）
  **一张空的日志表。** 接下来建触发器，让 orderitems 每次插入都自动往这里写一行。

  【二】AFTER INSERT：插入之后自动记账
  SQL> CREATE TRIGGER trg_items_insert AFTER INSERT ON orderitems BEGIN INSERT INTO audit_log (action,
       detail) VALUES ('insert', NEW.order_num || '/' || NEW.prod_id); END
  完成
  SQL> INSERT INTO orderitems (order_num, order_item, prod_id, quantity,
       item_price) VALUES (20005, 5, 'SAFE', 1, 50.00)
  影响 1 行
  SQL> SELECT * FROM audit_log
  id     action     detail
  -----------------------------
  1      insert     20005/SAFE
  （1 行）
  **我只插了 orderitems 一行，日志表自己多了一条。** 这就是触发器的意思：
  **事件驱动，不需要谁去调用。**
  **`NEW` 指新插入的那一行**，`NEW.order_num` 取它的列值；
  **DELETE 的触发器里用 `OLD`**（被删掉的那行）。

  【三】BEFORE DELETE：把要删的先存起来
  SQL> CREATE TABLE items_deleted (order_num INTEGER, prod_id TEXT, quantity INTEGER,
       item_price REAL)
  完成
  SQL> CREATE TRIGGER trg_items_delete BEFORE DELETE ON orderitems BEGIN INSERT INTO items_deleted VALUES (OLD.order_num,
       OLD.prod_id, OLD.quantity, OLD.item_price); END
  完成
  SQL> DELETE FROM orderitems WHERE order_num = 20005 AND prod_id = 'SAFE'
  影响 1 行
  SQL> SELECT * FROM items_deleted
  order_num     prod_id     quantity     item_price
  --------------------------------------------------
  20005         SAFE        1            50.00
  （1 行）
  **删掉的这行被完整留了底。** 用 BEFORE 是为了「删之前先复制」——
  **AFTER DELETE 的时候行已经没了，OLD 也就取不到了**（多数引擎如此）。

  【四】BEFORE INSERT + RAISE：当场拦下不合规数据
  SQL> CREATE TRIGGER trg_qty_check BEFORE INSERT ON orderitems WHEN NEW.quantity <= 0 BEGIN SELECT RAISE(ABORT,
       '数量必须大于 0'); END
  完成
  **插入被拒：数量必须大于 0**
  **这条比 CHECK 约束灵活**：约束只能看本行的列，
  **触发器还能查别的表、写别的表、给出自己的错误信息。**
  SQL> SELECT COUNT(*) AS n FROM orderitems WHERE order_num = 20005
  n
  --
  4
  （1 行）
  **20005 还是四行**——它本来就是四行，刚才插的 SAFE 在第三段被删掉了，
  **所以这里是「没多也没少」。**

  【五】看一眼清单，再删掉一个
  SQL> SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger' ORDER BY name
  name                 tbl_name
  --------------------------------
  trg_items_delete     orderitems
  trg_items_insert     orderitems
  trg_qty_check        orderitems
  （3 行）
  SQL> DROP TRIGGER trg_qty_check
  完成
  SQL> SELECT name FROM sqlite_master WHERE type = 'trigger' ORDER BY name
  name
  -----------------
  trg_items_delete
  trg_items_insert
  （2 行）
  **触发器也有名字，能被查到、能被删掉。** 删的时候只删触发器，表和数据都不动。

  【六】MySQL 侧的写法与限制（教材原码，不执行）
  `CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';`
  `DROP TRIGGER newproduct;`
  **`FOR EACH ROW` 说明 MySQL 只支持行级触发器**，一次插一百行就触发一百次。
  **SQL Server 有语句级触发器，一次只触发一次——两边语义不同。**
  **MySQL 还有个硬限制：触发器里不能改「触发它的那张表」**，
  **否则会陷入自己触发自己的循环。** 这一条踩过的人不少。

  【七】触发器该不该用
  **好处是「不会忘」**：谁插数据都会触发，不依赖应用代码守规矩。
  **坏处有三个**：**行为是隐式的**（看 INSERT 的代码根本不知道还改了别的表）；
  **难调试**（出错信息往往指不到触发点）；**有性能代价**（每次写都多一遍动作）。
  **教材的态度是「能用约束就别用触发器」**：
  **NOT NULL、主键、CHECK 这些约束更简单、更透明、更快。**

第一段建日志表。 空的，0 行。 第二段插一行 orderitems。 日志表自己多了一条 20005/SAFE，这就是 AFTER INSERT 的效果。 第三段删一行。 被删的 SAFE 整行进备份表：20005／1 件／50.00。 第四段拦人。 数量 0 的行被拒， 错误信息是我在触发器里写的。 20005 仍是四行，非法数据确实没进去。 第五段看清单。 三个触发器都挂在 orderitems 上，删掉一个之后剩两个。 第六段 MySQL 写法。 FOR EACH ROW 说明只有行级；不能在触发器里改本表。 第七段讲代价。 隐式、难调试、有性能代价。

30 秒实验

1. 再插一行 orderitems，日志表会多几条？为什么不是两条？
2. 把 BEFORE DELETE 改成 AFTER DELETE，备份表还写得进去吗？
3. （先想再跑）删掉 trg_items_insert 之后再插一行，日志表会变吗？

常见误区

1. 在触发器里改「触发它的表」。MySQL 直接禁止，否则会无限递归。
2. 用 AFTER DELETE 去取 OLD。行已经没了，多数引擎取不到。
3. 用触发器代替约束。约束更快更透明，触发器是给它补位的，不是替代品。

触发器能在事件发生时做事， 但一件事拆成多条语句时，怎么保证「全成或全不成」？ 下一章讲事务——数据安全的最后一层。

• ☐ （本章完，下一章 第26章 管理事务处理）

相关笔记

• 第24章 使用游标
• 第26章 管理事务处理
• 15 课 · 数据库基础 · 触发器
• 🐬 MySQL必知必会 · 索引