数据库原理与选型 / 系统课时
触发器与自动化数据规则
触发器与自动化数据规则
触发器把“发生某种数据变更时必须自动执行的规则”放进数据库。本节用一个账户示例串起 BEFORE 校验、AFTER 审计和 INSTEAD OF 视图写入,并验证它们的执行结果与适用边界。
学习目标
- 区分 BEFORE、AFTER 与 INSTEAD OF 触发器的执行位置
- 使用
NEW、OLD、WHEN与RAISE()实现校验和审计 - 通过视图触发器把外部写入转换为对基础表的受控操作
三类触发器分别解决什么
SQLite 触发器响应 INSERT、UPDATE 或 DELETE。对普通表,常用 BEFORE 在真正写入前做拒绝式校验,用 AFTER 在写入成功后记录审计或执行派生操作;INSTEAD OF 则用于视图,用自定义逻辑替代原本不可直接落到视图上的写操作。
在触发器体中,NEW.column 表示新值,OLD.column 表示旧值。INSERT 只有 NEW,DELETE 只有 OLD,UPDATE 两者都可用。WHEN 可以先过滤不需要执行的情况,RAISE(ABORT, ...) 可以终止当前语句并返回明确错误。

用一个示例串起校验、审计与视图写入
下面的脚本创建基础表、审计表和视图,再建立三个触发器。对视图更新余额时,INSTEAD OF 触发器把操作转到 accounts;随后 BEFORE 负责检查余额,AFTER 在真实更新完成后写入审计记录。
DROP VIEW IF EXISTS account_summary;
DROP TABLE IF EXISTS account_audit;
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
owner TEXT NOT NULL,
balance INTEGER NOT NULL
);
CREATE TABLE account_audit (
id INTEGER PRIMARY KEY,
account_id INTEGER NOT NULL,
old_balance INTEGER NOT NULL,
new_balance INTEGER NOT NULL,
changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts(id, owner, balance)
VALUES (1, 'Ada', 1000);
CREATE VIEW account_summary AS
SELECT id, owner, balance
FROM accounts;
CREATE TRIGGER accounts_balance_guard
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
SELECT RAISE(ABORT, 'balance cannot be negative');
END;
CREATE TRIGGER accounts_balance_audit
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
WHEN OLD.balance IS NOT NEW.balance
BEGIN
INSERT INTO account_audit(account_id, old_balance, new_balance)
VALUES (NEW.id, OLD.balance, NEW.balance);
END;
CREATE TRIGGER account_summary_update_balance
INSTEAD OF UPDATE OF balance ON account_summary
FOR EACH ROW
BEGIN
UPDATE accounts
SET balance = NEW.balance
WHERE id = OLD.id;
END;
UPDATE account_summary
SET balance = 900
WHERE id = 1;
SELECT id, owner, balance
FROM accounts;
SELECT account_id, old_balance, new_balance, changed_at
FROM account_audit;预期结果:
1|Ada|900
1|1000|900|<TIME><TIME> 是 SQLite 写入审计记录时生成的时间,实际值会不同。这个结果同时证明了三件事:视图更新被成功转写、基础表余额发生变化、AFTER 触发器拿到了更新前后的值。
验证失败路径
把余额改成负数时,INSTEAD OF 触发器仍会尝试更新基础表,但 BEFORE 触发器会在写入前终止该语句,因此 AFTER 审计也不会执行。
UPDATE account_summary
SET balance = -50
WHERE id = 1;预期输出:
Runtime error: balance cannot be negative不同 SQLite 客户端可能在错误文本前添加行号或错误码,但核心错误消息应为 balance cannot be negative。再次查询 accounts 时余额仍为 900,审计表也不会新增这次失败更新的记录。
设计触发器时要守住的边界
触发器适合保存必须与数据写入同时生效的局部规则,例如审计记录、简单一致性校验或视图写入转换。它不适合承载长耗时任务、远程调用或复杂业务流程,因为这些逻辑隐藏在 SQL 写入背后,会增加事务耗时和排查难度。
SQLite 的 BEFORE 触发器更适合“检查并拒绝”,不要把它当成修改 NEW 行内容的通用机制;需要在写入后派生数据时优先使用 AFTER。对 UPDATE OF balance 还要注意:它判断的是 UPDATE 语句是否包含该列,因此示例额外用 WHEN OLD.balance IS NOT NEW.balance 避免值未变化时产生无意义审计。
当规则属于数据库必须维护的一致性时,触发器能把约束放到所有写入入口之前;当规则需要跨服务协调、异步执行或复杂重试时,应留在应用层或任务系统中。