Test SQLite audit triggers for iOS on a cloud Mac

When a project moves from business-logic development on Windows to building an iOS app on a cloud Mac, database updates may appear to work while the audit table records extra entries, misses changes, or even leaves the UI showing the wrong state after a rollback. Rather than waiting for full-build acceptance testing, isolate the SQLite triggers in a minimal, executable fixture, then rerun the tests through the connection the app actually uses.

Define what an audit entry represents first

This article records only committed title changes—not every time a user clicks Save. Writing back the same value must not create an entry; changing a string to NULL must create one; and rolling back a business transaction must also roll back its audit entries. Define these three requirements as a contract before implementing the trigger.

Have the sqlite3 command-line tool ready on the cloud Mac and run sqlite3 --version to record its version. If the app bundles a database library, also run SELECT sqlite_version() through the app’s connection. The command-line version does not establish which version actually runs on iOS.

A trigger that writes to a regular audit table runs in the same transaction as the business-data write. It is suitable for tracking committed changes, not for independently preserving failed attempts, and it does not provide tamper-proof security auditing.

Use only synthetic records for testing; do not copy the production database. Old and new values in the audit table may contain user content and must not be retained indefinitely merely to make troubleshooting easier.

Build a SQL fixture that fails explicitly

Save the following as trigger-check.sql. The temporary assertion table uses a constraint to turn incorrect counts into SQL errors. Printing query results without checking the exit code does not create an effective test gate.

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

Run sqlite3 :memory: < trigger-check.sql. A successful run may produce no output; a failed run should return a nonzero exit code. The dot command here belongs to the command-line shell and cannot be passed unchanged to the app’s database execution API.

IS NOT is essential to this example: it distinguishes NULL from a non-NULL value. With != instead, comparisons involving NULL do not evaluate to true, so audit entries may be missed.

Use negative controls to prove the test gate works

First remove the WHEN condition and rerun the fixture. The first no-op update should cause an assertion failure. Next change the condition to OLD.title != NEW.title; the NULL-transition test should fail. If neither defect is detected, check whether the script is swallowing the exit code or whether it is still executing an older file.

Column names in UPDATE OF are another trap: SQLite allows nonexistent column names in the definition and will not reject trigger creation solely because of a typo. Temporarily change title in the example’s UPDATE OF clause to titel. An actual title update should then fail the test because its audit entry is missing.

Do not just count triggers. Read their actual definitions from sqlite_schema, check the columns with PRAGMA table_info(note), and make at least one effective change to every audited field. That is how you catch triggers that exist but do not work.

Include recursion and multi-row updates in acceptance testing

The current trigger writes only to the audit table and does not modify note again, so it does not depend on self-recursion. Run the fixture with recursion set to both OFF and ON; the results should be identical. Explicitly set this option and read it back when initializing the app’s connection rather than relying on tool defaults.

If a trigger is later added to “correct the title after an update,” writing back to the source table may introduce recursion or extra audit entries. Do not try to hide the problem by changing the recursion depth. First decide whether normalization happens before the app writes the value or is handled by a single trigger.

Next, insert several records with different primary keys and change all their titles with one update statement. SQLite triggers execute once per row. Verify the audit contents for each primary key rather than assuming audit sequence numbers reflect the order of business processing. Include separate cases for an empty string, NULL, and an update that matches no records.

Complete the final checks through the iOS connection

Once the command-line fixture passes, move table creation and the test operations into database integration tests, using the project’s actual database wrapper and transaction API. Check the return code for every execution; do not turn constraint errors into empty results. After an expected failure, also verify that the connection can still execute queries.

Finally, inject an audit-write failure: in a dedicated test database, add a constraint that rejects a specific new value, then update the business table. Confirm that the update returns an error and that neither the business value nor the audit entry is partially committed. The caller must then explicitly end or roll back the transaction; do not assume every error automatically rolls back the entire transaction.

Retain the fixture, the actual trigger definitions, the database versions from both environments, and the results of the failure cases as deliverables. Complete these checks during the rental period, and the next time you switch machines, you can simply rerun the same contract tests rather than judging audit reliability by whether the Save button reported an error.

Frequently asked questions