Create Triggers with Functions

Picture this: you just finished building a sleek new PostgreSQL schema, your API is humming, and then — a message slips in. A user deletes their account, but their orders, payments, and audit logs still linger, quietly corrupting reports. Or a critical status column updates, yet no one gets notified, and the downstream pipeline churns out stale data. Without automation, every one of these workflows becomes a manual, error-prone chore scattered across your application code. This is exactly the problem CREATE TRIGGER solves in PostgreSQL, and in this lesson you'll master the art of creating triggers that fire trigger functions automatically, keeping your data consistent and your business logic reliable.

The problem this lesson solves

Applications rarely live in isolation. Every insert, update, or delete often demands side effects: validating a row before it's saved, maintaining an audit trail, updating a materialized counter, or syncing to another service. Doing all that in application code is fragile — you must remember to call helper functions everywhere, and if one code path forgets, you get silent data corruption.

Triggers move this logic into the database itself. Instead of trusting every developer to follow convention, you define a trigger that fires a trigger function automatically whenever a specified event occurs. The database becomes the gatekeeper, ensuring that every operation — regardless of origin — follows the same rules.

For example, imagine an inventory table that tracks product quantities. When a new sale is inserted, you want stock to drop instantly. Without a trigger, your app must update both tables in a transaction — easy to forget or get wrong. With a trigger, PostgreSQL handles it atomically, so your data stays correct even if an application has a bug.

Beyond consistency, triggers are essential for auditing, soft deletes, denormalized caching, and enforcing complex business rules that are hard to express with simple constraints. They are a cornerstone of robust, production-grade PostgreSQL design.

Core concept / mental model

Think of a trigger as a watchdog stationed at a table. It watches for a specific event (like INSERT, UPDATE, or DELETE) and, when that event happens, it runs a pre-defined trigger function. The trigger function is a normal PL/pgSQL (or another language) function, but it's special because it can inspect the old and new versions of the row and even modify the data before it's stored.

Here's a simple diagram in words:

[Application] --> SQL statement --> PostgreSQL core
                                      |
                                  [Trigger]
                                      |
                              [Trigger function]
                                      |
                                  (side effects)

The key distinction: trigger vs. trigger function. The function contains the logic; the trigger is the wiring that links the event to the function. You can create many triggers pointing to the same function, each with different timing or event conditions.

PostgreSQL supports two timing options: - BEFORE: fires before the row is inserted, updated, or deleted. Great for validation, defaults, or transforming data. - AFTER: fires after the operation completes. Perfect for audit logs, cascade updates, or external notifications.

Additionally, you can choose row-level triggers (fires once per affected row) or statement-level triggers (fires once per SQL statement, even if it affects 100 rows). Row-level triggers are the most common because they give you access to NEW and OLD row records.

Think of it like a home security system: the sensor (trigger) detects motion (event) and sends a signal to the control panel (trigger function), which then performs the programmed action (like recording footage or calling the police). The sensor doesn't need to know how to record; it just knows something happened.

How it works step by step

Creating a trigger in PostgreSQL always involves three steps:

1. Create the trigger function using CREATE FUNCTION. Inside the function, you can use the special variables NEW (the new row being inserted/updated) and OLD (the previous version of the row being updated/deleted). The function must return a row — typically NEW or OLD — or NULL to cancel the operation (for BEFORE triggers).
2. Create the trigger on a table using CREATE TRIGGER, specifying the event, timing, and which function to call. You can optionally add a WHEN clause to narrow the condition.
3. Test by performing DML operations and observing the side effects.

Let's walk through a canonical example: auditing changes to a users table.

First, create the audit table and trigger function:

-- Audit table
CREATE TABLE user_audit (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    action TEXT NOT NULL,  -- 'INSERT', 'UPDATE', 'DELETE'
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Trigger function
CREATE OR REPLACE FUNCTION audit_user_change()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO user_audit(user_id, action) VALUES (OLD.id, 'DELETE');
        RETURN OLD;
    ELSE
        INSERT INTO user_audit(user_id, action) VALUES (NEW.id, TG_OP);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

Then attach the trigger to the users table:

CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_user_change();

Now, any operation on users will automatically record an audit entry.

The key to mastery is understanding the execution order of multiple triggers: they fire alphabetically by trigger name when defined on the same table and event. If your logic depends on order, name your triggers accordingly (e.g., trg_a_..., trg_b_...).

Also, note that a trigger function can return NULL to silently skip the operation. This is a powerful technique for conditional validation: if you don't want the insert to happen, return NULL, and the row is skipped without an error.

Hands-on walkthrough

Let's build something practical: a trigger that automatically updates an aggregate order_totals table whenever a new order_item is inserted. This keeps summary data fresh without extra application code.

Setup

-- Core tables
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE,
    product_name TEXT NOT NULL,
    quantity INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL
);

-- Summary table
CREATE TABLE order_totals (
    order_id BIGINT PRIMARY KEY REFERENCES orders(id) ON DELETE CASCADE,
    total NUMERIC(12,2) NOT NULL DEFAULT 0
);

Trigger function and trigger

CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
    -- For INSERT/UPDATE, add the new line amount
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_totals(order_id, total)
        VALUES (NEW.order_id, NEW.quantity * NEW.unit_price)
        ON CONFLICT (order_id) DO UPDATE
        SET total = order_totals.total + EXCLUDED.total;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE order_totals
        SET total = total - OLD.quantity * OLD.unit_price
        WHERE order_id = OLD.order_id;
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_items_after
AFTER INSERT OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

Test it

INSERT INTO orders(id) VALUES (1);
INSERT INTO order_items(order_id, product_name, quantity, unit_price)
VALUES (1, 'Widget', 3, 19.99);

SELECT * FROM order_totals;

Expected output:

 order_id | total
----------+-------
        1 | 59.97
(1 row)

Now delete that item:

DELETE FROM order_items WHERE id = 1;
SELECT * FROM order_totals;

Expected output:

 order_id | total
----------+-------
        1 |  0.00
(1 row)

Great — the total stays consistent automatically.

Pro tip: use WHEN to limit firing

If you only want to update the total when the quantity changes, add a condition to the trigger:

CREATE TRIGGER trg_order_items_update
AFTER UPDATE OF quantity, unit_price ON order_items
FOR EACH ROW
WHEN (OLD.quantity IS DISTINCT FROM NEW.quantity OR
      OLD.unit_price IS DISTINCT FROM NEW.unit_price)
EXECUTE FUNCTION update_order_total();

This avoids unnecessary work on unrelated column updates.

Compare options / when to choose what

Triggers are not the only way to automate database actions. Let's compare them with alternatives:

Triggers win when you need guaranteed, transactional side effects. For example, an updated_at timestamp that always refreshes on any update is a classic trigger use case. A generated column can't do that across different tables.

However, don't overuse triggers. They are hidden logic — future developers might be confused. Document them well and keep them small. For simple values derived from the same row, prefer generated columns. For cross-table calculations on demand, use views.

Troubleshooting & edge cases

My trigger didn't fire — why? - Did you specify the correct event (INSERT, UPDATE, DELETE)? A trigger on UPDATE won't fire on INSERT. - Check if a WHEN clause is too restrictive. If you filter on a column that doesn't change, the trigger won't run. - Ensure the trigger is actually created on the right table. Use \d table_name in psql to inspect triggers.

My trigger function returns an error like "column NEW does not exist"? You must declare the function with RETURNS TRIGGER and use the NEW/OLD records. In PL/pgSQL, NEW is a record variable, not a row reference — you access fields with NEW.column_name. Double-check your syntax.

Trigger fires but the side effect is wrong (e.g., total doubled)? Classic mistake: running the trigger on both INSERT and UPDATE without handling the logic differently. For instance, if you always add the new amount, an UPDATE that only changes the quantity will add the new amount to the existing total instead of replacing it. Use IF TG_OP = 'UPDATE' to recalculate from scratch, or use OLD/NEW comparisons.

Infinite recursion! When a trigger function runs an UPDATE on the same table that fired it, it can trigger itself again. Use a WHEN clause to exclude the update, or modify the function to avoid recursion (e.g., set a flag). For example:

CREATE TRIGGER prevent_empty_username
BEFORE UPDATE OF username ON users
FOR EACH ROW
WHEN (NEW.username IS NULL OR NEW.username = '')
EXECUTE FUNCTION raise_exception();

Trigger execution order issues Multiple triggers on the same event fire alphabetically. If order matters, name them with prefixes like trg_01_..., trg_02_.... Otherwise, combine logic into a single function.

Performance degradation Row-level triggers on huge UPDATE statements can slow battleship operations. Consider statement-level triggers if you don't need per-row access, or restrict triggers to rare events using WHEN.

What you learned & what's next

In this lesson, you've mastered the core of CREATE TRIGGER and trigger functions. You can now: - Explain the difference between trigger and trigger function. - Write a trigger function that uses NEW, OLD, TG_OP to implement logic. - Create BEFORE/AFTER row-level triggers with optional WHEN conditions. - Understand the execution order and how to avoid recursion. - Choose between triggers, generated columns, and application code based on the scenario.

This is a monumental step in your PostgreSQL journey — you've unlocked the ability to enforce business rules directly in the database, making your applications more robust and maintainable.

Your next lesson in this track builds on this foundation: creating triggers with trigger functions in more advanced scenarios, such as auditing entire schema changes, replicating data, or implementing soft deletes with triggers. You'll also explore PL/pgSQL control structures that make trigger functions even more powerful.

Continue to the next lesson to solidify your understanding and learn how to manage triggers at scale. See you there!