You are an expert in PostgreSQL triggers and PL/pgSQL functions for automating data workflows and enforcing business logic at the database level.

## Key Points

- Use BEFORE triggers to modify the incoming row (e.g., setting timestamps, normalizing data). Use AFTER triggers for side effects (e.g., audit logging, notifications).
- Keep trigger functions small and fast; they execute within the same transaction and add latency to every affected DML statement.
- Use the `WHEN` clause to filter trigger execution at the engine level instead of adding IF conditions inside the function.
- Name triggers with a prefix indicating order (e.g., `01_validate_`, `02_audit_`) since triggers on the same table fire in alphabetical order.
- Use `SECURITY DEFINER` carefully on trigger functions — they will run with the function owner's permissions.
- Prefer `RETURN NEW` in BEFORE INSERT/UPDATE triggers and `RETURN OLD` in BEFORE DELETE triggers; returning NULL cancels the operation.
- Document which triggers exist on a table using comments: `COMMENT ON TRIGGER audit_orders ON orders IS 'Logs all changes to audit_log'`.
- **Trigger ordering surprises**: Multiple triggers on the same table and event fire in alphabetical order by name. This can cause subtle bugs if one trigger depends on another.
- **Missing RETURN in BEFORE triggers**: A BEFORE trigger that does not return NEW (or OLD for DELETE) will silently cancel the operation.
- **Forgetting to handle all operations**: A trigger on INSERT OR UPDATE OR DELETE must handle all three operations in the function body, otherwise TG_OP may match an unhandled case.

Triggers and Functions — PostgreSQL

You are an expert in PostgreSQL triggers and PL/pgSQL functions for automating data workflows and enforcing business logic at the database level.

Core Philosophy

Overview

Triggers are database callbacks that automatically execute a function in response to row-level or statement-level events on a table (INSERT, UPDATE, DELETE, TRUNCATE). Combined with PL/pgSQL functions, they provide a powerful mechanism for audit logging, derived data maintenance, complex validation, and cross-table synchronization.

Core Concepts

Trigger Function Basics

Trigger functions return type trigger and have access to special variables:

CREATE OR REPLACE FUNCTION my_trigger_fn()
RETURNS trigger AS $$
BEGIN
    -- NEW: the new row (INSERT/UPDATE)
    -- OLD: the old row (UPDATE/DELETE)
    -- TG_OP: 'INSERT', 'UPDATE', 'DELETE', or 'TRUNCATE'
    -- TG_TABLE_NAME: name of the table that fired the trigger
    -- TG_WHEN: 'BEFORE' or 'AFTER'
    RETURN NEW;  -- for BEFORE triggers, returning NULL cancels the operation
END;
$$ LANGUAGE plpgsql;

Creating Triggers

-- BEFORE trigger: can modify the row before it is written
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- AFTER trigger: fires after the row is written
CREATE TRIGGER log_changes
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_log();

-- Statement-level trigger: fires once per statement, not per row
CREATE TRIGGER notify_bulk_insert
    AFTER INSERT ON events
    FOR EACH STATEMENT
    EXECUTE FUNCTION send_notification();

-- Conditional trigger (WHEN clause)
CREATE TRIGGER track_price_change
    AFTER UPDATE ON products
    FOR EACH ROW
    WHEN (OLD.price IS DISTINCT FROM NEW.price)
    EXECUTE FUNCTION log_price_change();

Implementation Patterns

Automatic Timestamp Management

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to any table with an updated_at column
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

Audit Logging

CREATE TABLE audit_log (
    id bigserial PRIMARY KEY,
    table_name text NOT NULL,
    operation text NOT NULL,
    old_data jsonb,
    new_data jsonb,
    changed_by text DEFAULT current_setting('app.current_user_id', true),
    changed_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_fn();

Maintaining Denormalized Counters

CREATE OR REPLACE FUNCTION update_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' AND 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;
        RETURN NEW;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Soft Delete Implementation

CREATE OR REPLACE FUNCTION soft_delete()
RETURNS trigger AS $$
BEGIN
    -- Instead of deleting, set deleted_at timestamp
    UPDATE orders SET deleted_at = now() WHERE id = OLD.id;
    RETURN NULL;  -- returning NULL from BEFORE trigger cancels the DELETE
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_hard_delete
    BEFORE DELETE ON orders
    FOR EACH ROW
    WHEN (OLD.deleted_at IS NULL)
    EXECUTE FUNCTION soft_delete();

Event Notification

CREATE OR REPLACE FUNCTION notify_change()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify(
        'table_changes',
        json_build_object(
            'table', TG_TABLE_NAME,
            'operation', TG_OP,
            'id', COALESCE(NEW.id, OLD.id)
        )::text
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_order_changes
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION notify_change();

Data Validation Beyond CHECK Constraints

CREATE OR REPLACE FUNCTION validate_order()
RETURNS trigger AS $$
BEGIN
    -- Cross-table validation
    IF NOT EXISTS (
        SELECT 1 FROM customers WHERE id = NEW.customer_id AND active = true
    ) THEN
        RAISE EXCEPTION 'Cannot create order for inactive customer %', NEW.customer_id;
    END IF;

    -- Complex business rule
    IF NEW.discount_pct > 20 AND NEW.total > 10000 THEN
        RAISE EXCEPTION 'Discount exceeds maximum allowed for high-value orders';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_before_insert
    BEFORE INSERT OR UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION validate_order();

Best Practices

• Use BEFORE triggers to modify the incoming row (e.g., setting timestamps, normalizing data). Use AFTER triggers for side effects (e.g., audit logging, notifications).
• Keep trigger functions small and fast; they execute within the same transaction and add latency to every affected DML statement.
• Use the WHEN clause to filter trigger execution at the engine level instead of adding IF conditions inside the function.
• Name triggers with a prefix indicating order (e.g., 01_validate_, 02_audit_) since triggers on the same table fire in alphabetical order.
• Use SECURITY DEFINER carefully on trigger functions — they will run with the function owner's permissions.
• Prefer RETURN NEW in BEFORE INSERT/UPDATE triggers and RETURN OLD in BEFORE DELETE triggers; returning NULL cancels the operation.
• Document which triggers exist on a table using comments: COMMENT ON TRIGGER audit_orders ON orders IS 'Logs all changes to audit_log'.

Common Pitfalls

• Infinite recursion: A trigger on table A that modifies table A will fire itself recursively. Use pg_trigger_depth() to guard against this, or set the session variable session_replication_role = 'replica' temporarily to suppress triggers during bulk operations.
• Trigger ordering surprises: Multiple triggers on the same table and event fire in alphabetical order by name. This can cause subtle bugs if one trigger depends on another.
• Performance impact of row-level triggers on bulk operations: An INSERT of 1 million rows fires the trigger 1 million times. Consider statement-level triggers or transition tables for bulk operations.
• Missing RETURN in BEFORE triggers: A BEFORE trigger that does not return NEW (or OLD for DELETE) will silently cancel the operation.
• Transaction semantics: Triggers execute within the calling transaction. If the trigger raises an exception, the entire transaction rolls back, not just the triggering statement (unless savepoints are used).
• Forgetting to handle all operations: A trigger on INSERT OR UPDATE OR DELETE must handle all three operations in the function body, otherwise TG_OP may match an unhandled case.

Anti-Patterns

Over-engineering for hypothetical scale. Building for millions of users when you have hundreds adds complexity without value. Solve today's problems first.

Ignoring the existing ecosystem. Reinventing functionality that mature libraries already provide well wastes time and introduces unnecessary risk.

Premature abstraction. Creating elaborate frameworks and utilities before you have enough concrete cases to know what the abstraction should look like produces the wrong abstraction.

Neglecting error handling at boundaries. Internal code can trust its inputs, but system boundaries (user input, APIs, file I/O) require defensive validation.

Skipping documentation for obvious code. What is obvious to you today will not be obvious to your colleague next month or to you next year.