云端 Mac 上的 iOS SQLite 触发器审计验收

项目在 Windows 上完成业务开发，迁到云端 Mac 出 iOS 包时，数据库更新看似正常，审计表却可能多记、漏记，甚至在回滚后让界面显示错误状态。与其等到整包验收再排查，不如先把 SQLite 触发器拆成可执行的最小夹具，再通过应用实际使用的连接复跑。

先定义审计记录代表什么

本文只记录“已经提交的标题变化”，不是记录用户每次点击保存。相同值写回不新增日志，字符串变成空值需要记录，业务事务撤销时日志也必须撤销。三个条件先写成契约，再实现触发器。

准备云端 Mac 上的 sqlite3 命令行工具，执行 sqlite3 --version 留下版本。应用若内置数据库库文件，还要在应用连接中查询 SELECT sqlite_version()；命令行版本不能代表 iOS 实际运行版本。

触发器写入普通审计表，与业务写入处在同一事务中。它适合追踪已提交变化，不适合独立保存失败尝试，也不构成不可篡改的安全审计。

测试只使用人工记录，不复制生产数据库。审计表里的旧值和新值可能包含用户内容，不能为了方便排障无期限保留。

建立可以直接失败的 SQL 夹具

把下面内容保存为 trigger-check.sql。临时断言表通过约束把错误计数变成 SQL 错误；只打印查询结果而不检查退出码，不能形成门禁。

.bail on
PRAGMA recursive_triggers = OFF;

CREATE TABLE note (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE note_audit (
    seq INTEGER PRIMARY KEY,
    note_id INTEGER NOT NULL,
    old_title TEXT,
    new_title TEXT
);

CREATE TRIGGER note_title_audit
AFTER UPDATE OF title ON note
WHEN OLD.title IS NOT NEW.title
BEGIN
    INSERT INTO note_audit(note_id, old_title, new_title)
    VALUES(NEW.id, OLD.title, NEW.title);
END;

CREATE TEMP TABLE assertion (
    ok INTEGER NOT NULL CHECK(ok = 1)
);

INSERT INTO note VALUES(1, 'draft');
UPDATE note SET title = title WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 0 FROM note_audit;

UPDATE note SET title = NULL WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 1 FROM note_audit;
INSERT INTO assertion
SELECT old_title IS 'draft' AND new_title IS NULL
FROM note_audit WHERE seq = 1;

UPDATE note SET title = NULL WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 1 FROM note_audit;

UPDATE note SET title = 'ready' WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 2 FROM note_audit;

BEGIN;
UPDATE note SET title = 'temporary' WHERE id = 1;
ROLLBACK;

INSERT INTO assertion
SELECT title IS 'ready' FROM note WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 2 FROM note_audit;

执行 sqlite3 :memory: < trigger-check.sql，成功时可以没有输出，失败时应返回非零退出码。这里的点命令属于命令行外壳，不能原样传给应用的数据库执行接口。

IS NOT 是本例的关键：它能区分空值与非空值。改成 != 后，涉及 NULL 的比较不会得到真值，日志就可能漏写。

用反例证明门禁确实有效

先删除 WHEN 条件重跑，第一条无变化更新就应触发断言失败。再把条件改成 OLD.title != NEW.title，空值转换用例应失败。若两种错误都没有被发现，检查脚本是否吞掉退出码，或者实际执行的是否仍是旧文件。

另一个陷阱是 UPDATE OF 中的列名：SQLite 允许定义里出现不存在的列名，不会仅因拼写错误拒绝创建触发器。把示例中的 title 临时改成 titel，真正更新标题时应因日志缺失而失败。

不要只检查触发器数量。通过 sqlite_schema 读取实际定义，配合 PRAGMA table_info(note) 核对列，再让每个被审计字段至少发生一次有效变化，才能覆盖“存在但不工作”的情况。

把递归与多行更新纳入验收

当前触发器只写审计表，不会再次修改 note，因此不依赖自递归。分别在递归开关为 OFF 和 ON 时执行夹具，结果都应一致。应用连接初始化时显式设置并读回该开关，不依赖工具默认值。

如果后来增加“更新后修正标题”的触发器，重新写入源表就可能形成递归或额外日志。不要靠调整递归深度掩盖问题，应先明确规范化发生在应用写入前，还是由单一触发器负责。

再插入多条不同主键记录，用一条更新语句修改全部标题。SQLite 触发器逐行执行，应核对每个主键对应的日志内容，而不是假设日志序号能代表业务处理顺序。空字符串、空值、未命中任何记录也应各有用例。

在 iOS 连接中完成最后一轮检查

命令行夹具通过后，把建表和操作拆到数据库集成测试中，使用项目真实的数据库封装和事务接口。读取每次执行的返回码，不把约束错误转换成空结果；遇到预期失败时，还要验证连接能够继续执行查询。

最后补一次审计写入失败注入：在专用测试库里增加拒绝特定新值的约束，再更新业务表。确认更新返回错误，业务值与日志均未部分提交；随后由调用方明确结束或回滚事务，不能假设所有错误都会自动撤销整个事务。

交付时保留夹具、实际触发器定义、两端数据库版本和失败用例结果。租期内完成这些检查后，下一次换机器只需复跑同一组契约，不必凭“保存按钮没报错”判断审计是否可靠。

常见问题