Chapter 15 · Views, Routines, and Database Automation
Triggers, Side Effects, and Audit Patterns
A trigger runs because another statement changed data. That automatic execution is valuable for atomic audit and invariants, but it is also hidden control flow. Good trigger design therefore minimizes scope and maximizes visibility.
Learning outcomes
Use automatic database actions without losing control-flow clarity
Describe trigger event, timing, granularity, condition, and action.
Use OLD and NEW row values correctly in SQLite.
Implement narrow audit, invariant, and writable-view patterns.
Recognize recursion, ordering, performance, and debugging hazards.
Choose constraints or explicit application commands when they are clearer than triggers.
A trigger is hidden control flow
A trigger attaches automatic work to a database event. SQLite supports triggers for INSERT, UPDATE, and DELETE; they execute per affected row. PostgreSQL additionally supports statement-level triggers and transition tables.
The initiating statement and trigger actions share one transaction: a trigger error can reject the original write.
Trigger anatomy
| Dimension | Examples | Design question |
|---|---|---|
| Event | INSERT, UPDATE, DELETE | Which mutations should activate the logic? |
| Timing | BEFORE, AFTER, INSTEAD OF | Must the logic validate, observe the final row, or adapt a view write? |
| Granularity | Per row; PostgreSQL can also be per statement | How many times can it run for one command? |
| Condition | WHEN predicate | Can irrelevant rows be excluded before actions run? |
| Context | OLD and/or NEW | Which before/after values are valid for the event? |
| Action | SQL statements or trigger function | Can the effect be small, deterministic, and local? |
Audit only meaningful state changes
DROP TABLE IF EXISTS order_audit;CREATE TABLE order_audit ( audit_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, event_type TEXT NOT NULL, old_status TEXT, new_status TEXT, old_total_cents INTEGER, new_total_cents INTEGER, changed_at TEXT NOT NULL, actor TEXT NOT NULL) STRICT;DROP TRIGGER IF EXISTS trg_order_audit_update;CREATE TRIGGER trg_order_audit_updateAFTER UPDATE OF status, total_cents ON sales_orderWHEN OLD.status IS NOT NEW.status OR OLD.total_cents IS NOT NEW.total_centsBEGIN INSERT INTO order_audit ( order_id, event_type, old_status, new_status, old_total_cents, new_total_cents, changed_at, actor ) VALUES ( NEW.order_id, 'UPDATE', OLD.status, NEW.status, OLD.total_cents, NEW.total_cents, datetime('now'), 'database-trigger' );END;UPDATE sales_orderSET status = 'paid', total_cents = 8000, updated_at = datetime('now'), version = version + 1WHERE order_id = 102;SELECT * FROM order_audit ORDER BY audit_id;The WHEN clause prevents audit noise when an update statement names the monitored columns but leaves their values unchanged.
Reject an invalid transition
A table CHECK constraint can validate one row’s shape, but it cannot easily express a transition such as “a paid order cannot return to draft.” A narrow trigger can compare OLD and NEW.
DROP TRIGGER IF EXISTS trg_order_status_transition;CREATE TRIGGER trg_order_status_transitionBEFORE UPDATE OF status ON sales_orderWHEN OLD.status = 'paid' AND NEW.status IN ('draft', 'submitted')BEGIN SELECT RAISE(ABORT, 'paid orders cannot return to an open state');END;-- Expected failure:UPDATE sales_orderSET status = 'draft'WHERE order_id = 101;Make a view writable with INSTEAD OF
SQLite views are read-only, but an INSTEAD OF trigger can translate a view write into base-table changes. This creates a write API and must be documented separately from the read contract.
DROP VIEW IF EXISTS submitted_order_api;CREATE VIEW submitted_order_api ASSELECT order_id, customer_id, ordered_at, total_centsFROM sales_orderWHERE status = 'submitted';DROP TRIGGER IF EXISTS trg_submit_order_api_insert;CREATE TRIGGER trg_submit_order_api_insertINSTEAD OF INSERT ON submitted_order_apiBEGIN INSERT INTO sales_order ( order_id, customer_id, status, ordered_at, updated_at, total_cents, version ) VALUES ( NEW.order_id, NEW.customer_id, 'submitted', NEW.ordered_at, NEW.ordered_at, NEW.total_cents, 1 );END;INSERT INTO submitted_order_api (order_id, customer_id, ordered_at, total_cents)VALUES (107, 2, '2026-07-08 09:00:00', 6300);SELECT order_id, status, total_centsFROM sales_orderWHERE order_id = 107;Prefer declarative rules first
| Requirement | Preferred mechanism | Why |
|---|---|---|
| Value range | CHECK constraint | Visible in schema and applied to every writer. |
| Uniqueness | UNIQUE constraint/index | Concurrency-safe and optimizer-visible. |
| Parent existence | Foreign key | Standard referential semantics and actions. |
| Simple default | DEFAULT | No hidden control flow. |
| Cross-row transition | Trigger or explicit command | Needs old/new or related-row context. |
| External side effect | Application/outbox worker | Database transactions should not call unreliable external services directly. |
Trigger hazards
Recursion
A trigger writes a table that activates itself or another trigger cycle. Bound the graph and test recursion settings.
Write amplification
One bulk statement may fire row logic thousands of times. Measure both latency and log volume.
Ordering
Multiple triggers may have vendor-specific ordering. Avoid correctness that depends on implicit order.
Invisible effects
Developers see one statement but many rows change. Inventory triggers and surface them in migration review.
Unexpected aborts
A trigger error rejects the initiating statement. Return actionable messages and test failure paths.
Deployment coupling
Trigger definitions and application expectations must be versioned and released compatibly.
Audit integrity and limitations
An audit trigger records database-visible changes atomically, but it does not automatically know the authenticated user, request identifier, or business reason. Supply trustworthy context through connection/session facilities where supported, or use an explicit command that writes both domain data and audit data.
BEGIN;SET LOCAL app.actor_id = 'user-427';SET LOCAL app.request_id = 'req-01K1ABC';UPDATE app.sales_orderSET status = 'cancelled'WHERE order_id = 104;COMMIT;-- A trigger function can read:-- current_setting('app.actor_id', true)-- current_setting('app.request_id', true)Test every event path
-- No audit row for a no-op update.SELECT COUNT(*) AS before_count FROM order_audit;UPDATE sales_orderSET total_cents = total_centsWHERE order_id = 103;SELECT COUNT(*) AS after_count FROM order_audit;-- Inspect trigger definitions during review.SELECT name, tbl_name, sqlFROM sqlite_schemaWHERE type = 'trigger'ORDER BY name;Check your understanding
- Why is a trigger considered hidden control flow?
- When is
INSTEAD OFappropriate? - Why should a value-range rule usually be a constraint instead?
- What context is often missing from an automatic audit trigger?
Review the answers
The client statement does not show the automatic actions. INSTEAD OF adapts writes to a view or unsupported target. Constraints are clearer, declarative, and universally enforced. Authenticated actor, request identity, and business reason usually originate outside the database statement unless passed explicitly.
Summary and references
- Specify event, timing, granularity, condition, context, and action for every trigger.
- Keep triggers narrow, deterministic, local, and easy to discover.
- Use constraints before triggers for declarative invariants.
- Audit meaningful changes and pass trustworthy request context explicitly.
- Test bulk writes, recursion, failure messages, retries, and deployment compatibility.