6月1日 00:06

SQLite 触发器怎么写，哪些场景要慎用？

SQLite 触发器是在表或视图发生 INSERT、UPDATE、DELETE 时自动执行的一段 SQL。它常用来做审计日志、轻量数据校验、维护冗余字段，或者配合视图实现写入转发。好处是规则离数据很近，不容易被某个业务入口漏掉；坏处是逻辑藏在数据库里，排查问题时经常被忽略。实际项目里，触发器适合放稳定、短小、强约束的逻辑，不适合承载复杂业务流程。

sql
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, updated_at TEXT);
CREATE TABLE audit_log(id INTEGER PRIMARY KEY, table_name TEXT, op TEXT, row_id INTEGER, created_at TEXT);

CREATE TRIGGER users_audit_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(table_name, op, row_id, created_at)
  VALUES ('users', 'UPDATE', NEW.id, datetime('now'));
END;

追问

BEFORE 和 AFTER 触发器怎么选？

BEFORE 更适合做校验，发现非法数据就阻止写入；AFTER 更适合做日志、同步、汇总这类依赖最终写入结果的动作。SQLite 里不要照搬其他数据库的写法，例如 SET NEW.updated_at = ... 不是合法语法。取舍是能用 CHECK、外键、唯一索引解决的，优先用内置约束。否则规则分散在约束、触发器和业务代码里，后期很难判断到底是谁拦住了写入。

sql
CREATE TRIGGER users_name_required
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.name IS NULL OR length(trim(NEW.name)) = 0
BEGIN
  SELECT RAISE(ABORT, 'name is required');
END;

如何用触发器维护更新时间？

SQLite 不能在 BEFORE UPDATE 里直接给 NEW.updated_at 赋值，很多人会在这里踩坑。可用做法是用 AFTER UPDATE 再更新同一行，但一定要加 WHEN 条件，避免无意义的循环更新。这个方案简单，代价是一次业务更新会多一次写操作。高写入频率场景下，更建议应用层显式写入 updated_at，触发器只作为兜底。

sql
CREATE TRIGGER users_touch_updated_at
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

触发器适合做审计日志吗？

适合，但要控制日志内容和写入成本。触发器做审计的优势是所有入口都会被记录，不管写入来自接口、脚本还是迁移工具。取舍在于日志表会不断变大，影响备份体积和查询性能。建议只记录主键、操作类型、关键字段差异和时间，不要把整行大字段都塞进去。

sql
CREATE TRIGGER users_audit_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(table_name, op, row_id, created_at)
  VALUES ('users', 'DELETE', OLD.id, datetime('now'));
END;

触发器会不会递归执行？

SQLite 有递归触发器开关，可以用 PRAGMA recursive_triggers 查看或设置，但项目里不要依赖模糊默认值。更稳的方式是在触发器上写清楚 WHEN 条件，让它只在必要时执行。边界是触发器里更新同一张表最容易形成循环，尤其是维护计数、更新时间这类逻辑。只要出现“触发器更新表，更新又触发同一个触发器”，就应该重新审视设计。

sql
PRAGMA recursive_triggers = OFF;
SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'trigger';
DROP TRIGGER IF EXISTS users_audit_update;

什么时候不应该用触发器？

如果逻辑依赖外部服务、权限上下文、复杂分支或用户操作意图，就不适合放进触发器。触发器看不到完整业务语义，只看到某一行数据发生了变化。短期把复杂业务藏进去会少写代码，长期会让调试变成猜谜。比较稳的边界是：数据库完整性和审计可以放触发器，业务流程编排留在应用层。

SQLite 触发器最好小而明确：校验就校验，审计就审计，别顺手塞进一整段业务。它能让数据规则更可靠，也可能让问题更隐蔽，关键在于把边界画清楚。

标签：Sqlite