SQLite Basics: Record Row Changes with Triggers

A trigger runs SQL automatically when a table changes. It can keep a small change log consistent even when several application paths write to the same table. These examples use AFTER triggers on toy data, with visible queries showing what each automatic action produced.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Log each inserted row

Save as insert-log.sql:

CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE task_events (task_id INTEGER, event TEXT);
CREATE TRIGGER log_task_insert
AFTER INSERT ON tasks
BEGIN
    INSERT INTO task_events VALUES (NEW.id, 'created');
END;
INSERT INTO tasks VALUES (1, 'Write guide'), (2, 'Check examples');
SELECT task_id, event FROM task_events ORDER BY task_id;

Run:

python3 run-sql.py insert-log.sql

Output:

1|created
2|created

One INSERT adds two tasks, and the trigger writes two events. SQLite triggers run per affected row, not once per statement. NEW identifies the inserted row; there is no previous row to read with OLD for this event.

The runner’s complete_statement() check matters here. The semicolon inside BEGIN/END does not finish CREATE TRIGGER. The whole definition must reach END; before it can be executed. Splitting the input at every semicolon would break this example.

2. Ignore updates that keep the same value

Save as status-log.sql:

CREATE TABLE tasks (id INTEGER PRIMARY KEY, status TEXT);
CREATE TABLE status_events (task_id INTEGER, before_status TEXT, after_status TEXT);
CREATE TRIGGER log_status_change
AFTER UPDATE OF status ON tasks
WHEN OLD.status IS NOT NEW.status
BEGIN
    INSERT INTO status_events VALUES (NEW.id, OLD.status, NEW.status);
END;
INSERT INTO tasks VALUES (1, 'queued'), (2, NULL);
UPDATE tasks SET status = 'queued' WHERE id = 1;
UPDATE tasks SET status = 'done' WHERE id = 1;
UPDATE tasks SET status = 'queued' WHERE id = 2;
SELECT task_id, before_status, after_status FROM status_events ORDER BY task_id;

Run:

python3 run-sql.py status-log.sql

Output:

1|queued|done
2|NULL|queued

UPDATE OF status checks whether status is assigned by the statement. It does not prove that the value changed. The WHEN condition handles that distinction, using IS NOT so a transition from NULL is detected too.

Both OLD and NEW are available during an UPDATE. The first update produces no event; the next two do. Test this no-change case when adding a trigger, since logging every repeated assignment can create misleading noise. Also check column spelling: SQLite historically accepts unknown names in UPDATE OF rather than rejecting the definition.

3. Roll back a deletion and its log together

Save as delete-log.sql:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
CREATE TABLE deleted_notes (id INTEGER, body TEXT);
CREATE TRIGGER archive_note
AFTER DELETE ON notes
BEGIN
    INSERT INTO deleted_notes VALUES (OLD.id, OLD.body);
END;
INSERT INTO notes VALUES (1, 'Temporary draft');
BEGIN;
DELETE FROM notes WHERE id = 1;
SELECT 'during', COUNT(*) FROM deleted_notes;
ROLLBACK;
SELECT 'restored', COUNT(*) FROM notes;
SELECT 'log after rollback', COUNT(*) FROM deleted_notes;

Run:

python3 run-sql.py delete-log.sql

Output:

during|1
restored|1
log after rollback|0

DELETE provides OLD because the row is being removed. The trigger’s insert participates in the same transaction: rolling back restores the note and removes the archive entry. A trigger-based log therefore does not preserve evidence of rolled-back attempts.

Keep trigger bodies small and test their side effects alongside the original operation. This archive is also not a tamper-proof audit system: someone with sufficient database access can change the log or remove the trigger. For parent-child consistency, use foreign keys rather than rebuilding referential rules by hand.

SQLite’s CREATE TRIGGER reference covers OLD, NEW, WHEN, and trigger limitations. These examples avoid modifying the triggering row inside a BEFORE trigger, and do not depend on several triggers firing in a particular order.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py insert-log.sql status-log.sql delete-log.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.