Skip to content
Tutorials Center
My learning

Topics

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.

SQL Audit prices and deletions ยท database: shop

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.

SQL Maintain stock when items are sold ยท database: shop

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).

SQL Reject invalid changes (fails on purpose) ยท database: shop

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

SQL Maintain updated_at ยท database: company

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

SQL
-- 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.