Right, so you want to use triggers. Excellent choice, or perhaps a necessary evil. Either way, you’re here because you need the database to do something automatically, something integral enough to your data’s integrity that you can’t trust the application layer to always get it right. Let’s talk about the three most common jobs we give these digital automatons: keeping a secret history, stamping time on everything like an overzealous bureaucrat, and maintaining derived data so you don’t have to.

The Eternal Ledger: Change Auditing

The most classic, can’t-live-without-it use for a BEFORE trigger is building an audit trail. The idea is simple: anytime a row in a critical table is changed, you want a permanent record of who did it, when, and what the old values were. You could do this in your app code, but then you have to remember to do it every single time you write a query against that table. Miss one spot? History has a gap. A trigger ensures this happens relentlessly, for every change, no matter where it comes from (your app, a cron job, a dev connecting directly via psql at 3 AM… we’ve all been there).

You’ll typically create a separate audit table that mirrors the original but adds metadata columns. The trigger itself is a BEFORE UPDATE OR DELETE trigger. Why BEFORE? Because we need to see the old values before they are gone forever. We use the OLD record.

-- First, create the table we want to audit. Let's use 'accounts' because money focuses the mind.
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    balance NUMERIC(15, 2) NOT NULL DEFAULT 0
);

-- Now, the audit log table. Note the extra columns for our metadata.
CREATE TABLE account_audit_log (
    id SERIAL PRIMARY KEY,
    operation CHAR(1) NOT NULL, -- 'U' for update, 'D' for delete
    changed_by TEXT NOT NULL DEFAULT CURRENT_USER,
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    old_data JSONB -- Storing the entire old row as JSON is flexible and easy
);

-- The trigger function that does the heavy lifting.
CREATE OR REPLACE FUNCTION log_account_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO account_audit_log (operation, old_data)
        VALUES ('U', to_jsonb(OLD));
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO account_audit_log (operation, old_data)
        VALUES ('D', to_jsonb(OLD));
    END IF;
    -- For an UPDATE, we still return NEW to allow the change to proceed.
    -- For a DELETE, there's no NEW, so we just return OLD? No. The manual says...
    -- "The return value of a row-level trigger fired AFTER or BEFORE DELETE
    -- must be NULL." So we handle that.
    RETURN COALESCE(NEW, OLD);
END;
$$;

-- Attach the function to the table as a BEFORE trigger.
CREATE TRIGGER trigger_account_audit
    BEFORE UPDATE OR DELETE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION log_account_change();

Pitfall: This can generate a lot of data very quickly. For highly volatile tables, consider a strategy where you only log the specific columns that matter instead of the entire OLD row, or look into built-in features like Postgres’s Audit Trigger Extension (pgaudit).

The Relentless Clock: Automatic Timestamps

This one is so common it’s almost a rite of passage. You want updated_at to always reflect the last moment the row was touched. It’s a BEFORE UPDATE trigger’s job to slam that timestamp right before the write happens.

-- Our table with the timestamp columns
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- The function. Beautifully simple.
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Just forcefully set the updated_at field to *right now*.
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

-- The trigger. Note it's ONLY on UPDATE.
CREATE TRIGGER trigger_posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Why BEFORE? Because we need to modify the NEW row data before it’s sent to the table. An AFTER trigger wouldn’t be able to change the row contents. The beauty here is its absolute reliability. No application code can “forget” to set the timestamp.

The Denormalization Dilemma: Maintaining Derived Data

Sometimes, for performance or simplicity, you denormalize. You store a value that could be calculated on the fly because calculating it on the fly a million times a second is a terrible idea. The canonical example is a counter cache. Imagine you have posts and comments. You might want to store the comment count on the post itself to avoid a COUNT(*) on the comments table every time you list posts.

This is where you need to be careful. The trigger isn’t on the table holding the derived data (posts), but on the source table (comments). And you need triggers for INSERT, UPDATE (if the UPDATE could change the post_id foreign key!), and DELETE.

-- Let's set the stage
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    comment_count INTEGER NOT NULL DEFAULT 0 -- our derived column
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    body TEXT NOT NULL
);

-- The function must handle all three operations.
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- If an INSERT happened, increment the count on the NEW post_id.
    IF (TG_OP = 'INSERT') THEN
        UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;

    -- If a DELETE happened, decrement the count on the OLD post_id.
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;

    -- This is the tricky one. If the UPDATE changed the post_id, we need to...
    -- decrement the count on the OLD post_id and increment on the NEW one.
    ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.post_id IS DISTINCT FROM 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;
    END IF;

    -- This is an AFTER trigger, so the return value is mostly ignored.
    RETURN NULL;
END;
$$;

-- This needs to be an AFTER trigger because we want the main operation (the insert/delete/update)
-- to be completed and visible before we recalculate the count.
CREATE TRIGGER trigger_comments_count
    AFTER INSERT OR UPDATE OF post_id OR DELETE ON comments
    FOR EACH ROW
    EXECUTE FUNCTION update_comment_count();

Major Pitfall Alert: This introduces a potential deadlock scenario. Two transactions inserting comments on different posts are fine. But if two transactions are inserting comments on the same post, the second one will be blocked trying to get a lock on that posts row until the first commits. For high-concurrency environments, you might need a more sophisticated strategy. This is the trade-off for denormalization: you’re exchanging write performance for read performance. Choose wisely.