Database Triggers

Also Known As

TL;DR

Explanation

Triggers execute SQL logic automatically when data changes. Common legitimate uses: audit trails (log who changed what and when), denormalised counter updates (post_count on user table), enforcing complex constraints not expressible as check constraints, and maintaining summary tables. Dangers: invisible side effects (application writes one row, trigger writes five more), performance surprises (a simple INSERT triggers complex logic), testing difficulty (tests must set up trigger state), and debugging complexity (unexpected data changes have invisible causes).

Diagram

sequenceDiagram
    participant APP as PHP App
    participant DB as Database
    participant TRG as Trigger
    participant LOG as Audit Log
    APP->>DB: UPDATE orders SET status='shipped'
    DB->>TRG: AFTER UPDATE fires automatically
    TRG->>LOG: INSERT into order_audit_log
    TRG-->>DB: complete
    DB-->>APP: 1 row updated
    Note over APP,TRG: App wrote 1 row<br/>trigger wrote 1 more<br/>developer may not know

Common Misconception

Why It Matters

Common Mistakes

• Complex business logic in triggers — impossible to unit test and invisible to application developers.
• Triggers that call external services — synchronous triggers block the transaction; use the outbox pattern.
• Cascading triggers — trigger A fires trigger B which fires trigger C; debugging is nearly impossible.
• Not documenting that triggers exist — future developers modify the table unaware of side effects.

Code Examples

-- Trigger hiding business logic:
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders FOR EACH ROW
BEGIN
    -- Sends email (makes HTTP call from trigger!)
    -- Updates inventory across 3 tables
    -- Calculates customer loyalty points
    -- Updates denormalised reporting tables
END;
-- Developer INSERT INTO orders... has no idea this fires

-- Trigger for legitimate audit only:
CREATE TRIGGER audit_order_updates
AFTER UPDATE ON orders FOR EACH ROW
BEGIN
    INSERT INTO order_audit_log
        (order_id, old_status, new_status, changed_at, changed_by)
    VALUES (OLD.id, OLD.status, NEW.status, NOW(), @current_user);
END;
-- Business logic stays in PHP where it's testable and visible

References

• ↗ https://www.postgresql.org/docs/current/trigger-definition.html

Tags

Related Terms