雲端 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，成功時可以沒有輸出，失敗時則應傳回非零結束碼。這裡的點命令屬於命令列 Shell，不能原封不動傳給應用程式的資料庫執行介面。

IS NOT 是本例的關鍵：它能區分空值與非空值。若改成 !=，涉及 NULL 的比較就不會得到真值，可能導致稽核紀錄漏寫。

用反例證明驗收關卡確實有效

先刪除 WHEN 條件再重跑，第一筆未改變值的更新就應觸發斷言失敗。接著將條件改成 OLD.title != NEW.title，空值轉換的測試案例應該失敗。如果這兩種錯誤都沒被抓到，請檢查指令稿是否忽略了結束碼，或實際執行的是否仍是舊檔案。

另一個陷阱是 UPDATE OF 中的欄位名稱：SQLite 允許定義中出現不存在的欄位名稱，不會只因拼字錯誤就拒絕建立觸發器。暫時將範例中 UPDATE OF 的 title 改成 titel，真正更新標題時，測試就應因缺少稽核紀錄而失敗。

不要只檢查觸發器數量。應透過 sqlite_schema 讀取實際定義，搭配 PRAGMA table_info(note) 核對欄位，再讓每個受稽核欄位至少發生一次有效異動，才能涵蓋「存在卻不會運作」的情況。

將遞迴與多筆更新納入驗收

目前的觸發器只寫入稽核表，不會再次修改 note，因此不依賴自我遞迴。分別在遞迴設定為 OFF 和 ON 時執行測試夾具，結果都應一致。初始化應用程式連線時，應明確設定並讀回此選項，不要依賴工具的預設值。

如果日後新增「更新後修正標題」的觸發器，重新寫入來源資料表就可能造成遞迴或額外紀錄。不要靠調整遞迴深度掩蓋問題；應先釐清正規化是在應用程式寫入前完成，還是交由單一觸發器負責。

接著插入多筆主鍵不同的資料，以一條更新陳述式修改全部標題。SQLite 觸發器會逐筆執行，因此應核對每個主鍵對應的稽核內容，而不是假設紀錄序號能代表業務處理順序。空字串、空值，以及未符合任何資料的更新，也都應各有測試案例。

在 iOS 連線中完成最後一輪檢查

命令列測試夾具通過後，將建表與操作拆入資料庫整合測試，使用專案實際採用的資料庫封裝與交易介面。檢查每次執行的回傳碼，不要將約束錯誤轉成空結果；遇到預期失敗時，也要確認連線仍能繼續執行查詢。

最後再做一次稽核寫入失敗注入：在專用測試資料庫中加入拒絕特定新值的約束條件，再更新業務資料表。確認更新傳回錯誤，且業務值與稽核紀錄都沒有部分提交；接著由呼叫端明確結束或回復交易，不能假設所有錯誤都會自動回復整筆交易。

交付時保留測試夾具、實際觸發器定義、兩端的資料庫版本，以及失敗案例的結果。在租期內完成這些檢查後，下次更換機器只需重跑同一組契約測試，不必再憑「儲存按鈕沒有報錯」判斷稽核是否可靠。

常見問題