SQL Reference ยท JSON and Special Features
SQL trigger patterns
Concept guide to common SQL trigger patterns: audit logs, keeping derived data in sync, validation and updated_at timestamps, with pitfalls and dialect notes.
A trigger runs SQL automatically when a row is inserted, updated or deleted. Used sparingly, triggers enforce rules no matter which application or script touches the data. Used carelessly, they hide logic where nobody looks and make writes slow. This page collects the patterns that triggers are good at. The statement itself is covered in CREATE TRIGGER.
Inside a row-level trigger, you can read the row before and after the change: OLD (the previous values, for UPDATE and DELETE) and NEW (the new values, for INSERT and UPDATE). The examples use SQLite syntax, which runs here; the dialect section shows the others.
Pattern 1: audit log
Record who changed what, and the old and new values, in a separate table that the application never edits directly.
Press Run (or Ctrl + Enter) to see the result here.
The WHEN condition keeps the log free of no-op updates. The timestamp comes from the column default, not from the trigger.
The DELETE in this example would fail on a database with foreign keys switched on if orders reference product 5. The sample database leaves PRAGMA foreign_keys off.
Pattern 2: keep derived data in sync
Store a total or a counter that would be expensive to compute on every read, and let triggers maintain it.
Press Run (or Ctrl + Enter) to see the result here.
A complete version also needs an AFTER UPDATE trigger for changed quantities or products. That is the cost of this pattern: every write path must be covered, or the copy drifts.
Pattern 3: validation
A BEFORE trigger can reject a change with a message when a CHECK constraint cannot express the rule (for instance, because it needs another table).
Press Run (or Ctrl + Enter) to see the result here.
The statement fails with Error: a cancelled order cannot be reopened, the message from RAISE(ABORT, ...), and no row changes.
Pattern 4: timestamps
Press Run (or Ctrl + Enter) to see the result here.
In real code, use CURRENT_TIMESTAMP instead of a fixed string (the fixed value just keeps this example repeatable). The WHEN clause stops the trigger from re-firing itself indefinitely: SQLite does not run recursive triggers by default, but other databases need care.
Dialect differences
-- PostgreSQL: a trigger function plus a trigger CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_touch BEFORE UPDATE ON departments FOR EACH ROW EXECUTE FUNCTION touch_updated_at(); -- MySQL: BEFORE trigger changes NEW directly CREATE TRIGGER trg_touch BEFORE UPDATE ON departments FOR EACH ROW SET NEW.updated_at = NOW(); -- SQL Server: statement-level, uses the inserted and deleted tables CREATE TRIGGER trg_touch ON dbo.departments AFTER UPDATE AS UPDATE d SET updated_at = SYSDATETIME() FROM dbo.departments d JOIN inserted i ON i.id = d.id;
| Topic | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Granularity | Row | Row or statement | Row | Statement | Row or statement |
| Old/new values | OLD, NEW |
OLD, NEW |
OLD, NEW |
deleted, inserted tables |
:OLD, :NEW |
| Modify the incoming row | Not in BEFORE; use AFTER + UPDATE |
NEW.col := ... in BEFORE |
SET NEW.col = ... in BEFORE |
INSTEAD OF or AFTER update |
:NEW.col := ... in BEFORE |
| Raise an error | RAISE(ABORT, 'msg') |
RAISE EXCEPTION |
SIGNAL SQLSTATE |
THROW |
RAISE_APPLICATION_ERROR |
Usage notes
- Prefer constraints first.
NOT NULL,CHECK, foreign keys and defaults are cheaper and visible in the schema. Use triggers for what they cannot express. - Keep them small and deterministic. A trigger runs inside the triggering statement's transaction; slow or failing triggers slow or fail the write.
- Cascades. Triggers can fire other triggers. Know the nesting and recursion settings of your database.
- Bulk loads. Row-level triggers multiply the cost of large
INSERTs; consider disabling them during imports where the database allows it. - Document them. Hidden side effects surprise people. List triggers in your schema docs.
- Testing. Test each trigger with the insert, update and delete cases and with multi-row statements.