Triggers — Automating Database Logic

A trigger is a function that PostgreSQL calls automatically when a specified event occurs on a table — an INSERT, UPDATE, or DELETE. Triggers execute PL/pgSQL code (PostgreSQL’s procedural language) that can inspect the old and new row values, raise errors, modify the operation, or write to other tables. They are the database’s own event system: when a comment is inserted, automatically increment the post’s comment_count; when a user is deleted, write an audit entry; when a post is published, set its published_at timestamp. Triggers enforce invariants that cannot be expressed as constraints, but they add hidden complexity — use them deliberately.

Trigger Basics #

-- Two parts: a trigger function and the trigger itself

-- ── Step 1: Create the trigger function ──────────────────────────────────────
-- Returns TRIGGER (special return type for trigger functions)
-- Can reference OLD (pre-change row) and NEW (post-change row)

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();  -- modify the row being inserted/updated
    RETURN NEW;              -- RETURN NEW applies the modified row
END;
$$ LANGUAGE plpgsql;

-- ── Step 2: Create the trigger ───────────────────────────────────────────────
CREATE TRIGGER posts_update_timestamp
    BEFORE UPDATE ON posts        -- BEFORE: can modify NEW; AFTER: sees committed data
    FOR EACH ROW                  -- ROW: fires once per row; STATEMENT: once per statement
    EXECUTE FUNCTION update_timestamp();

-- ── How OLD and NEW work ──────────────────────────────────────────────────────
-- INSERT: NEW = the row being inserted, OLD = NULL
-- UPDATE: NEW = the new values, OLD = the previous values
-- DELETE: NEW = NULL, OLD = the row being deleted

Counter Cache Trigger #

-- Maintain a denormalised comment_count on posts automatically

CREATE OR REPLACE FUNCTION update_post_comment_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        -- Comment moved to a different post (unusual but handle it)
        IF OLD.post_id != NEW.post_id THEN
            UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
            UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
        END IF;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER comments_count_trigger
    AFTER INSERT OR UPDATE OR DELETE ON comments
    FOR EACH ROW
    EXECUTE FUNCTION update_post_comment_count();

Conditional Trigger #

-- WHEN clause: only fire if condition is true
-- More efficient than checking in the function body

-- Only update published_at when status changes TO 'published'
CREATE OR REPLACE FUNCTION set_published_at()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'published' AND OLD.status != 'published' THEN
        NEW.published_at = NOW();
    ELSIF NEW.status != 'published' THEN
        NEW.published_at = NULL;   -- clear if unpublished
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_set_published_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status)   -- only fire when status changes
    EXECUTE FUNCTION set_published_at();

Managing Triggers #

-- List triggers on a table
SELECT trigger_name, event_manipulation, action_timing, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'posts';

-- Or using psql meta-command:
\d posts   -- shows triggers at the bottom

-- Disable a trigger (for bulk imports)
ALTER TABLE posts DISABLE TRIGGER posts_update_timestamp;
-- Do the bulk import here...
ALTER TABLE posts ENABLE TRIGGER posts_update_timestamp;

-- Drop a trigger
DROP TRIGGER IF EXISTS posts_update_timestamp ON posts;

-- Drop the trigger function
DROP FUNCTION IF EXISTS update_timestamp();

Common Mistakes #

Mistake 1 — BEFORE trigger returning NULL (silently cancels the operation) #

❌ Wrong — accidentally returns NULL, cancelling every INSERT:

CREATE OR REPLACE FUNCTION my_trigger() RETURNS TRIGGER AS $$
BEGIN
    -- forgot RETURN NEW!
    NEW.updated_at = NOW();
    -- implicit RETURN NULL cancels the INSERT/UPDATE!
END;
$$ LANGUAGE plpgsql;

✅ Correct — always return NEW (or OLD for DELETE):

BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;   -- ✓ essential for BEFORE triggers on INSERT/UPDATE
END;

Mistake 2 — Trigger causing cascading updates on high-write tables #

❌ Wrong — every comment INSERT triggers an UPDATE on posts (two writes per comment):

-- On a table with 10,000 comments/minute: 10,000 extra post UPDATEs/minute
-- May cause lock contention on popular posts

✅ Consider — batch counter updates asynchronously via background job for very high volume.

Mistake 3 — Forgetting to disable triggers during bulk imports #

❌ Wrong — bulk loading 1M rows fires triggers 1M times (very slow):

COPY posts FROM 'large_file.csv';   -- fires all triggers for every row!

✅ Correct — disable triggers for bulk load, then re-enable:

ALTER TABLE posts DISABLE TRIGGER ALL;
COPY posts FROM 'large_file.csv';
ALTER TABLE posts ENABLE TRIGGER ALL;
-- Then recalculate any counter caches that were not updated ✓

Quick Reference #