SQLite 可运行文档：触发器

Q: SQLite 中的触发器是什么？

触发器是一段 SQL，当表上发生特定事件（INSERT、UPDATE 或 DELETE）时会自动执行。你只需用 CREATE TRIGGER 定义一次，之后每当事件发生，SQLite 都会替你触发它。用它可以记录审计日志、维护派生列或强制执行规则，而不必依赖应用代码记得去做。

Q: BEFORE、AFTER 和 INSTEAD OF 触发器有什么区别？

BEFORE 在行的修改生效之前执行，适合做校验或调整这一行的数据。AFTER 在修改完成之后执行，适合记录日志或同步其他表。INSTEAD OF 只能用在视图上，它会完全替代原本要执行的操作，让视图变得可写。

Q: 在触发器里如何引用正在被修改的行？

在 INSERT 和 UPDATE 中用 NEW.column 引用新传入的行，在 UPDATE 和 DELETE 中用 OLD.column 引用原有的行。INSERT 触发器只能看到 NEW，DELETE 触发器只能看到 OLD，UPDATE 触发器两者都能看到。这些引用只作用于当前正在处理的那一行。

Q: SQLite 中如何列出或删除触发器？

触发器保存在 sqlitemaster 中，SELECT name, tblname FROM sqlitemaster WHERE type = 'trigger'; 会列出全部触发器。要删除一个，用 DROP TRIGGER triggername;，不确定它是否存在时用 DROP TRIGGER IF EXISTS trigger_name;。删除一张表也会同时删除它的触发器。

触发器会自动执行 SQL

触发器是一段存储起来的 SQL，每当某张表上发生特定事件时就会触发。你只写一次，“什么时候执行”交给 SQLite 处理。

基本形式如下：

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('Notebook', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'Notebook';
SELECT * FROM price_history;

我们从没显式地往 price_history 里 INSERT 过数据，是触发器做的。以后每一次价格更新都会以同样的方式记录下来，不管它来自命令行、脚本还是应用。

CREATE TRIGGER 的结构

逐个部分来看这个语法：

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- one or more statements
END;

时机：BEFORE 在修改之前执行，AFTER 在修改之后执行，INSTEAD OF 替代这次修改（仅限视图）。
事件：由哪种操作触发。UPDATE OF col1, col2 把更新范围缩小到特定的列。
表：被监视的那张表。
FOR EACH ROW：SQLite 只支持行级触发器，所以这一项是隐含的。为了清晰可以写上，但它不会改变任何行为。
WHEN：可选的条件。只有条件为真时，触发器主体才会执行。
主体：BEGIN 和 END 之间的一条或多条语句，每条都必须以分号结尾。

这就是全部语法。大多数实际的触发器只有五到十行。

OLD 和 NEW：正在被修改的行

在主体中，有两个伪行可以让你看到数据：

NEW：新传入的行。在 INSERT 和 UPDATE 触发器中可用。
OLD：原有的行。在 UPDATE 和 DELETE 触发器中可用。

DELETE 触发器只有 OLD，INSERT 触发器只有 NEW，UPDATE 触发器两者都有。

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

被删除的行已经从 accounts 中消失了，但它的数据在消失之前已被记录到 deletions 里。

BEFORE：校验或调整这一行

BEFORE 触发器在行的修改写入磁盘之前执行，很适合抛出错误或规范化数据：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- noop, just illustrating context
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'email cannot be empty');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

第二条 INSERT 在写入任何行之前就被中止了。RAISE(ABORT, '...') 会取消当前语句，并回滚到这条语句开始时的状态；RAISE(FAIL, ...)、RAISE(ROLLBACK, ...) 和 RAISE(IGNORE) 能让你更细致地控制后续行为。

如果只是单纯的数据校验，优先使用 CHECK 约束：它是声明式的，优化器也了解它。当规则需要查看其他表，或者要做 CHECK 表达不了的事情时，再用 BEFORE 触发器。

WHEN：带条件的触发器

WHEN 子句决定哪些行的修改会真正执行主体。它在 OLD 和 NEW 绑定之后、针对每一行求值：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

第一笔订单没有达到条件，另外两笔达到了。如果没有 WHEN 子句，每次插入都会写进 big_orders，你就得在读取时再去过滤。

INSTEAD OF：让视图可写

视图默认是只读的。INSTEAD OF 触发器会拦截对视图的写操作，转而执行你的 SQL，通常是把它转换成对底层表的写入：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

应用把这个视图当作一张表来操作，触发器在幕后负责把姓名拆分成 first_name 和 last_name。

列出和删除触发器

触发器和表、索引一起保存在 sqlite_master 中：

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- placeholder body
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; 是稳妥的写法。删除触发器所在的表时，触发器会被自动删除，不需要事先清理。

值得了解的陷阱

有几件事第一次用时很容易踩坑：

触发器按行触发，而不是按语句触发。 一条修改 1,000 行的 UPDATE 会触发 1,000 次。如果主体本身开销很大，累积起来会很快。
触发器在外层事务中执行。 如果外层语句回滚，触发器的写入也会一起回滚。这通常正是你想要的，但也意味着触发器不能用来实现“无论如何都要记下来”。
递归触发器默认关闭。 修改同一张表的触发器不会再次触发自己，除非你设置了 PRAGMA recursive_triggers = ON;。除非有明确的理由，否则保持关闭。
应用端的写入只有绕开数据库才能绕开触发器。 只要每次写入都经过 SQLite，触发器就会执行。通过原生 SQL 批量写入的 ORM 同样会触发它们。
不要把业务逻辑分散到许多触发器里。 在调用处根本看不到它们，排查“这一行为什么会出现？”的人只能去 grep sqlite_master。把触发器用在横切关注点上（审计日志、派生列、视图可写），其余逻辑留在应用代码中。

一个真实的审计日志示例

把前面的模式组合起来，记录 posts 表的每一次修改：

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('Draft', 'first cut');
UPDATE posts SET title = 'Published' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

一个触发器在同一处既保证了 updated_at 的准确，又写入了一条审计记录。执行 UPDATE 的应用代码完全不需要知道这两件事的存在。

下一步：JSON 支持

触发器负责围绕行事件的自动化。SQLite 进阶的下一部分是你能在一行里面存储什么：JSON。SQLite 提供了一整套 JSON 函数，让你不离开 SQL 就能查询和更新结构化数据，这就是下一页的内容。

常见问题

SQLite 中的触发器是什么？

触发器是一段 SQL，当表上发生特定事件（INSERT、UPDATE 或 DELETE）时会自动执行。你只需用 CREATE TRIGGER 定义一次，之后每当事件发生，SQLite 都会替你触发它。用它可以记录审计日志、维护派生列或强制执行规则，而不必依赖应用代码记得去做。

BEFORE、AFTER 和 INSTEAD OF 触发器有什么区别？

BEFORE 在行的修改生效之前执行，适合做校验或调整这一行的数据。AFTER 在修改完成之后执行，适合记录日志或同步其他表。INSTEAD OF 只能用在视图上，它会完全替代原本要执行的操作，让视图变得可写。

在触发器里如何引用正在被修改的行？

在 INSERT 和 UPDATE 中用 NEW.column 引用新传入的行，在 UPDATE 和 DELETE 中用 OLD.column 引用原有的行。INSERT 触发器只能看到 NEW，DELETE 触发器只能看到 OLD，UPDATE 触发器两者都能看到。这些引用只作用于当前正在处理的那一行。

SQLite 中如何列出或删除触发器？

触发器保存在 sqlite_master 中，SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger'; 会列出全部触发器。要删除一个，用 DROP TRIGGER trigger_name;，不确定它是否存在时用 DROP TRIGGER IF EXISTS trigger_name;。删除一张表也会同时删除它的触发器。

SQLite 触发器：CREATE TRIGGER 与 OLD/NEW 用法