Learn

A trigger is a stored procedure the DB runs automatically in response to INSERT, UPDATE, or DELETE on a table.

Why use triggers

• Audit logs: record who changed what, when
• Maintain denormalized fields: refresh last_modified automatically
• Enforce invariants the type system can't (cross-row checks)
• Cascade deletes / sync between tables

SQLite syntax

CREATE TRIGGER set_modified_time
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET modified_at = datetime('now')
    WHERE id = NEW.id;
END;

Key parts:

• BEFORE or AFTER — when to fire
• INSERT, UPDATE, or DELETE — what triggers it
• FOR EACH ROW — fires once per affected row (statement-level triggers in Postgres can fire once per query)
• OLD / NEW — references to the row before/after the change
  • INSERT: only NEW exists
  • DELETE: only OLD exists
  • UPDATE: both OLD and NEW

Audit trail trigger

CREATE TABLE users_audit (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    action TEXT,
    old_email TEXT,
    new_email TEXT,
    changed_at TEXT DEFAULT (datetime('now'))
);

CREATE TRIGGER log_email_changes
AFTER UPDATE OF email ON users
FOR EACH ROW
WHEN OLD.email IS NOT NEW.email
BEGIN
    INSERT INTO users_audit (user_id, action, old_email, new_email)
    VALUES (NEW.id, 'UPDATE', OLD.email, NEW.email);
END;

The WHEN clause filters which row events fire. OF email restricts to UPDATEs that touched the email column.

Cascading sync trigger

-- When an order is deleted, also delete its line items.
CREATE TRIGGER cascade_delete_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    DELETE FROM order_items WHERE order_id = OLD.id;
END;

(Better: use ON DELETE CASCADE foreign key. Triggers are for non-trivial side effects.)

Validation trigger (use sparingly)

CREATE TRIGGER prevent_negative_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
    SELECT RAISE(FAIL, 'balance cannot go negative');
END;

A BEFORE trigger that aborts the operation. Better than checking in app code if you want guaranteed enforcement (no app can bypass it).

But consider: a CHECK (balance >= 0) constraint is simpler and faster. Use triggers only when constraints don't suffice.

Postgres triggers

More powerful — triggers call functions written in PL/pgSQL or other languages:

CREATE FUNCTION update_modified_time() RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_modified
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_modified_time();

The function can do anything — call other functions, send NOTIFY messages, even make HTTP calls (with extensions).

Performance and pitfalls

Triggers slow writes — every UPDATE runs the trigger logic. For hot-write tables, measure carefully.

Triggers can hide behavior — code reviewers won't see them in your application code. Document them well, or push the logic up.

Trigger order matters — multiple triggers on the same event fire in alphabetical name order (Postgres) or undefined order (SQLite). Don't rely on subtle ordering.

Recursive triggers — BEFORE UPDATE on table A that updates table B that has a trigger updating table A → infinite loop.

Test the rollback path — if a trigger fails inside a transaction, the whole transaction rolls back. Make sure your app handles that.

Stored procedures

Functions / stored procedures (PL/pgSQL, MySQL routines) execute multiple SQL statements with control flow. They live in the DB. Useful for:

• Reducing app-DB round trips
• Sharing complex logic across many app codebases
• Enforcing business rules at the DB layer

Downsides: harder to version control, harder to test, hard to debug. Use sparingly.

App-level code is usually preferred for business logic. The DB is a data store, not your app's brain. Triggers and procedures are for the cases where putting logic IN the DB is genuinely the right call.