数据库原理与选型 / 系统课时

触发器与自动化数据规则

触发器与自动化数据规则

触发器把“发生某种数据变更时必须自动执行的规则”放进数据库。本节用一个账户示例串起 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, ...) 可以终止当前语句并返回明确错误。

BEFORE、INSTEAD OF 与 AFTER 触发器执行链路

用一个示例串起校验、审计与视图写入

下面的脚本创建基础表、审计表和视图,再建立三个触发器。对视图更新余额时,INSTEAD OF 触发器把操作转到 accounts;随后 BEFORE 负责检查余额,AFTER 在真实更新完成后写入审计记录。

sql
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;

预期结果:

text
1|Ada|900
1|1000|900|<TIME>

<TIME> 是 SQLite 写入审计记录时生成的时间,实际值会不同。这个结果同时证明了三件事:视图更新被成功转写、基础表余额发生变化、AFTER 触发器拿到了更新前后的值。

验证失败路径

把余额改成负数时,INSTEAD OF 触发器仍会尝试更新基础表,但 BEFORE 触发器会在写入前终止该语句,因此 AFTER 审计也不会执行。

sql
UPDATE account_summary
SET balance = -50
WHERE id = 1;

预期输出:

text
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 避免值未变化时产生无意义审计。

当规则属于数据库必须维护的一致性时,触发器能把约束放到所有写入入口之前;当规则需要跨服务协调、异步执行或复杂重试时,应留在应用层或任务系统中。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

触发器与自动化数据规则

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通