Triggers: BEFORE, AFTER, INSTEAD OF, OLD/NEW, and Side Effects

Learning outcomes

A trigger is SQL stored in the schema that runs automatically when a row-level INSERT, UPDATE, or DELETE event occurs. That automation can enforce a useful invariant close to the data—but it can also hide writes from the code path a developer is reading. The design goal is not “use triggers whenever possible.” It is “use a trigger when database-local automatic behavior is clearer and safer than distributing the rule across every writer.”

Trigger vocabulary before syntax

SQLite currently implements FOR EACH ROW trigger behavior, not a separate statement-level trigger that fires once for a 10,000-row UPDATE.

A useful trigger: record status transitions

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS maintenance_note;DROP TABLE IF EXISTS device;DROP TABLE IF EXISTS site;CREATE TABLE site(  site_id INTEGER PRIMARY KEY,  site_name TEXT NOT NULL UNIQUE);CREATE TABLE device(  device_id INTEGER PRIMARY KEY,  site_id INTEGER NOT NULL REFERENCES site(site_id),  device_code TEXT NOT NULL UNIQUE,  device_name TEXT NOT NULL,  status TEXT NOT NULL CHECK(status IN ('active','inspection_due','retired')),  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);CREATE TABLE maintenance_note(  note_id INTEGER PRIMARY KEY,  device_id INTEGER NOT NULL REFERENCES device(device_id) ON DELETE CASCADE,  noted_at TEXT NOT NULL,  note_text TEXT NOT NULL);INSERT INTO site(site_name) VALUES ('North Plant'),('Harbor Lab');INSERT INTO device(site_id,device_code,device_name,status) VALUES(1,'PUMP-007','Cooling Water Pump 7','active'),(1,'FAN-014','Exhaust Fan 14','inspection_due'),(2,'SENS-003','Vibration Sensor 3','active');INSERT INTO maintenance_note(device_id,noted_at,note_text) VALUES(1,'2026-08-10T08:30:00Z','Seal inspected; no leak found.'),(2,'2026-08-11T14:15:00Z','Belt tension below preferred range.'),(1,'2026-08-12T06:00:00Z','Vibration rechecked after shift start.');DROP TABLE IF EXISTS device_status_history;CREATE TABLE device_status_history(  history_id INTEGER PRIMARY KEY,  device_id INTEGER NOT NULL,  old_status TEXT NOT NULL,  new_status TEXT NOT NULL,  changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);CREATE TRIGGER device_status_auditAFTER UPDATE OF status ON deviceWHEN OLD.status IS NOT NEW.statusBEGIN  INSERT INTO device_status_history(device_id,old_status,new_status)  VALUES(NEW.device_id, OLD.status, NEW.status);END;UPDATE deviceSET status='inspection_due'WHERE device_code='PUMP-007';SELECT device_id,old_status,new_statusFROM device_status_history;

OLD.status is the value before the UPDATE and NEW.status is the value after it. The WHEN condition prevents audit noise when an UPDATE mentions status but leaves it unchanged.

UPDATE OF is a trigger-selection hint with a historical trap

UPDATE OF status means the trigger is considered only when status appears on the left side of the UPDATE's SET clause. SQLite documentation also records a historical compatibility quirk: a nonexistent column named in UPDATE OF is silently ignored instead of making CREATE TRIGGER fail. Therefore, migration/tests should exercise trigger behavior—not merely assume that successful trigger creation proves every column name was valid.

BEFORE versus AFTER: prefer predictable target-row behavior

SQLite supports BEFORE and AFTER triggers on ordinary tables. But current documentation explicitly cautions that if a BEFORE UPDATE/DELETE trigger modifies or deletes the row that the outer statement is about to change, subsequent behavior is undefined; NEW.rowid is also undefined in a BEFORE INSERT when the rowid was not explicitly assigned. SQLite therefore encourages programmers to prefer AFTER triggers over BEFORE triggers in such designs.

INSTEAD OF triggers make a view writable by defining the write yourself

Views are read-only in SQLite. An INSTEAD OF trigger can accept an INSERT/UPDATE/DELETE directed at a view and translate that intent into writes against base tables.

DROP VIEW IF EXISTS device_status_api;CREATE VIEW device_status_api(device_code,status) ASSELECT device_code,status FROM device;CREATE TRIGGER device_status_api_updateINSTEAD OF UPDATE OF status ON device_status_apiBEGIN  UPDATE device  SET status=NEW.status,      updated_at=CURRENT_TIMESTAMP  WHERE device_code=OLD.device_code;END;UPDATE device_status_apiSET status='active'WHERE device_code='FAN-014';SELECT device_code,status FROM device WHERE device_code='FAN-014';

No row is physically updated “inside the view.” The trigger body performs the real UPDATE. This can be useful for a narrow compatibility API, but it increases schema behavior that application developers must know exists.

Trigger side effects and affected-row counters

Chapter 6 taught that affected-row counters must be interpreted carefully. Trigger work reinforces that lesson. Direct-change counters do not necessarily include every auxiliary row changed by triggers or foreign-key actions, and INSTEAD OF trigger firings have special counting behavior. Validate business outcomes from required state/invariants, not from an assumption that one rowcount equals the complete side-effect graph.

Recursive triggers: inspect, do not assume the default

PRAGMA recursive_triggers;

Recursive-trigger support exists in modern SQLite, but current documentation says it was initially off for compatibility and may be changed by builds/future defaults. The safe production pattern is to avoid accidental recursive trigger designs and explicitly initialize any setting your application depends on. Trigger recursion is also bounded by compile-time/runtime depth limits.

Useful versus over-engineered automation

If you need an application workflow with notifications, HTTP calls, retries, permissions, and external orchestration, that is usually clearer in application code around an explicit transaction—not hidden in a trigger.

Lab: prove trigger atomicity and visible side effects

BEGIN;UPDATE deviceSET status='retired'WHERE device_code='SENS-003';SELECT device_code,statusFROM device WHERE device_code='SENS-003';SELECT old_status,new_statusFROM device_status_historyWHERE device_id=(SELECT device_id FROM device WHERE device_code='SENS-003')ORDER BY history_id;ROLLBACK;-- Both base-table and trigger-written history effects are undone.SELECT device_code,status FROM device WHERE device_code='SENS-003';SELECT COUNT(*) FROM device_status_historyWHERE device_id=(SELECT device_id FROM device WHERE device_code='SENS-003');

A trigger runs within the statement/transaction that caused it. Rolling back the outer transaction rolls back its database-local trigger effects too.

Verification checkpoint

Production judgment and bridge

Triggers automate behavior within one database connection and transaction. Lesson 3 expands the connection itself: one connection can attach additional SQLite database files and run cross-database queries. That is powerful for import, comparison, migration, and archives—but it does not turn SQLite into a distributed database.

Authoritative references

• SQLite — CREATE TRIGGER.
• SQLite — PRAGMA recursive_triggers.
• SQLite — CREATE VIEW.