Why audit triggers exist

Fix the value. Keep the paper trail.

A product landed on the books with 40,000 mg of sodium per serving, a physically impossible number. An analyst quietly corrects the row to 590 mg. The current number reads clean. The earlier value, the person who pushed the fix, and the time of the change all vanish in one UPDATE.

✕ Without an audit trigger

product_nutrition after UPDATE

product_id	sodium_mg
412	590

The earlier value: erased. The change time: unknown. If the fix is wrong: no rollback path.

✓ With an audit trigger

product_nutrition after UPDATE

product_id	sodium_mg
412	590

nutrition_audit auto-written

product_id	old	new	at
412	40,000	590	2025-11-12 14:32

How the trigger works

An inventory audit trigger in three steps.

No application code in the loop. The database writes the receipt for you.

01

UPDATE issued

An analyst, a script, or a vendor edits a value in product_nutrition. Fix a bad sodium reading, correct a typo, refresh a label.

→

02

Trigger fires

SQLite fires the trigger on the write. The body compares OLD.value to NEW.value and acts only when the two differ.

→

03

Audit row written

A new row lands in nutrition_audit with the old value, the new value, and a timestamp. The application stays out of the loop.

The trigger

SQL writes the receipt for every fix.

The whole audit story is one empty log table plus a trigger listening for one change. The feature ends there.

OLD and NEWPseudo-rows hold both versions of the record inside the trigger body.

WHEN clauseFilters out no-op UPDATEs so the audit table stays signal, not noise.

CURRENT_TIMESTAMPThe when of each change comes free from the database.

-- the log table
CREATE TABLE nutrition_audit (
    audit_id    INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id  INTEGER NOT NULL,
    old_value   REAL,
    new_value   REAL,
    changed_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

-- the trigger
CREATE TRIGGER trg_audit_sodium
AFTER UPDATE OF sodium_mg ON product_nutrition
FOR EACH ROW
WHEN OLD.sodium_mg IS NOT NEW.sodium_mg
BEGIN
    INSERT INTO nutrition_audit
        (product_id, old_value, new_value)
    VALUES
        (OLD.product_id, OLD.sodium_mg, NEW.sodium_mg);
END;

Demo

One UPDATE. The database writes its own receipt.

Analyst types

UPDATE product_nutrition
   SET sodium_mg = 590
 WHERE product_id = 412;
-- 1 row affected.

→

Database writes

nutrition_audit, entry #1

product_id412

columnsodium_mg

old value40,000 mg

new value590 mg

changed at2025-11-12 14:32

Paper trail

Every silent fix is a silent risk.

01 Reversibility

Undo without archaeology

If a fix turns out wrong, the earlier value is one SELECT away. You roll back in seconds.

02 Compliance

Regulators expect logs

Audit trails are baseline in finance, healthcare, and food labeling. The current state of a row tells half the story.

03 Attribution

Who changed the row

Timestamp first. A user_id column drops in next. You answer the question, who edited row 412 in March.

04 Anomaly

Patterns of change

One column getting hit every Monday is a story. The audit log surfaces data-quality problems before dashboards do.

Back to portfolio Full design doc