← Back to the section

A trigger is code inside the database that runs on its own when data changes. It sounds convenient: you insert a row, and the rest happens by itself. But more often this "magic" creates more problems than it solves. Let's work out how triggers work, where they belong, and where you are better off without them.

one UPDATE, three matching rows — FOR EACH ROW fires on each UPDATE orders SET status='PAID' 3 rows match the condition row 1 id=101 row 2 id=102 row 3 id=103 BEFORE ROW edits NEW table new version AFTER ROW writes the log audit_log UPDATE orders SET status='PAID' row 1id=101 row 2id=102 row 3id=103 BEFORE ROWedits NEW tablenew version AFTER ROWwrites the log row 1: PAID row 2: PAID row 3: PAID the function ran 6 times: BEFORE and AFTER on every row FOR EACH STATEMENTone call for the whole statement — and one log row

The BEFORE function gets to fix the row before it is written; the AFTER function sees it already written and appends the log entry. Both happen per row: one UPDATE over three rows calls the functions six times. FOR EACH STATEMENT is the same function with a single call for the whole statement.

How a trigger works

Every time someone changes a row in a table, PostgreSQL automatically calls a piece of PL/pgSQL code. That is a trigger, and it consists of two parts: a function and the trigger itself, which calls that function.

-- the function that runs on the event
CREATE OR REPLACE FUNCTION log_to_audit()
RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, now());
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- the trigger: function, table, event
CREATE TRIGGER tr_order_doc_audit
AFTER INSERT OR UPDATE OR DELETE ON order_doc
FOR EACH ROW EXECUTE FUNCTION log_to_audit();

When it fires

A trigger has two parameters that determine the moment it fires:

  • BEFORE — before the changes are written. The function can modify the values before they are saved, and its return value matters here: return NEW and the row is written with those edits, return NULL and the operation for that row silently does not happen.
  • AFTER — after the write. The function sees the row in its new shape and can no longer change it, and PostgreSQL ignores whatever it returns — hence the RETURN NULL above. "Already written" does not mean "committed": the transaction is still open, and if a rollback follows, both the change and everything the trigger did disappear with it.

And granularity:

  • FOR EACH ROW — the function is called separately for each changed row.
  • FOR EACH STATEMENT — the function is called once for the whole SQL statement, regardless of the number of rows. Such a function does not see the rows at all: NEW and OLD are not set in it. To reach the affected rows, the trigger is declared as AFTER with REFERENCING NEW TABLE AS new_rows (transition tables arrived in PostgreSQL 10).

Inside the function these variables are available: TG_OP (what happened: INSERT, UPDATE or DELETE), TG_TABLE_NAME (the table name), NEW (the new row values), OLD (the old ones).

What it looks like on a model

The price of granularity shows without a database: three rows and one UPDATE over them. It also shows how BEFORE edits a column that was never mentioned in the statement.

live example

import java.util.ArrayList;
import java.util.List;

public class TriggerDemo {

    static final String[] status = {"NEW", "NEW", "NEW"};
    static final int[] updatedAt = {0, 0, 0};
    static final List<String> audit = new ArrayList<>();
    static int calls;

    public static void main(String[] args) {
        update("PAID", 42, true);
        System.out.println("FOR EACH ROW:       function calls " + calls + ", log rows " + audit.size());
        calls = 0;
        audit.clear();
        update("SHIPPED", 77, false);
        System.out.println("FOR EACH STATEMENT: function calls " + calls + ", log rows " + audit.size());
        System.out.println("updated_at of row 1 = " + updatedAt[0] + ", though the statement never mentioned it");
    }

    static void update(String newStatus, int now, boolean forEachRow) {
        for (int i = 0; i < status.length; i++) {
            if (forEachRow) {
                calls++;
                updatedAt[i] = now;
            }
            status[i] = newStatus;
            if (forEachRow) {
                calls++;
                audit.add("row " + (i + 1) + " -> " + newStatus);
            }
        }
        if (!forEachRow) {
            calls++;
            audit.add("rows changed: " + status.length);
        }
    }
}
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

Three rows — six calls against one; over a million rows that is two million calls against one.

Why business logic in triggers is a bad idea

Logic in the database looks dependable: it runs even on an UPDATE typed straight into psql. In practice you pay for that.

Logic in a trigger is invisible. A developer writes UPDATE order_doc SET status = 'paid' and has no idea that five more functions just ran quietly.

Triggers are hard to test. You cannot run a unit test without a real database: you need integration tests with Testcontainers, and that is slower and heavier.

Versioning is awkward. The application code and the trigger live in different places, and rolling back a release means rolling back the migration with the trigger separately — easy to forget.

Coupling to PostgreSQL. Logic in PL/pgSQL does not move to another database without a rewrite.

So the main principle is: business logic lives in application code. Triggers are the exception, not the rule.

Common mistakes: what people do with triggers but shouldn't

Updating updated_at via a trigger

This is the most widespread mistake.

-- common mistake — a trigger for updated_at
CREATE TRIGGER tr_set_updated_at
BEFORE UPDATE ON order_doc FOR EACH ROW EXECUTE FUNCTION set_updated_at();

The problem: a developer writes UPDATE order_doc SET status = 'paid' WHERE id = 1 and has no idea that the trigger quietly changes updated_at. When something goes wrong, the cause has to be hunted in the schema, not in the code.

The right way: set DEFAULT now() in the schema and state updated_at = now() explicitly in the statement.

CREATE TABLE order_doc (
    id         bigint PRIMARY KEY,
    status     text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- the statement in the code — explicit, nothing hidden
UPDATE order_doc SET status = ?, updated_at = now() WHERE id = ?;

Data validation via a trigger

For simple constraints there are CHECK conditions — they are clearer and faster than a trigger.

-- correct: a CHECK right in the schema
ALTER TABLE order_doc
    ADD CONSTRAINT ck_order_status CHECK (status IN ('new', 'paid', 'shipped'));

A more complex rule (say, "no more than one doctor is on duty at any moment") is better checked in application code with SELECT FOR UPDATE: the logic is easier to follow and to test.

Trigger chains

One trigger calls another, that one calls a third: untangling such a chain while debugging is painful. Replace it with an explicit event handler in the code.

Notifications and HTTP calls from a trigger

NOTIFY from a trigger is fragile: if the receiver is not listening to the channel at that moment, the message is simply lost. Instead the event is written to a separate table, and a background process delivers it.

When a trigger is justified

There are not many situations where a trigger wins.

A change log that cannot be bypassed

In a regulated industry — a bank, healthcare — every change to the data has to be recorded. The tr_order_doc_audit trigger from the start of the article does that no matter who came to change the data: even a manual UPDATE from psql does not slip past the log.

The guarantee is not absolute: the table owner can switch the trigger off with ALTER TABLE ... DISABLE TRIGGER, and TRUNCATE fires only statement-level triggers — row-level ones do not run on it.

The alternative — outbox plus application code — is more flexible, but it rests on discipline: the log entry has to be remembered every time. A trigger takes that responsibility off the team.

The database as the source of truth for several applications

If several applications in different languages connect to one database, shared logic in a trigger is the only place where it runs regardless of who made the change.

Denormalization with a consistency guarantee

If you need a counter that is always accurate right at the moment of the transaction (with no delay), a trigger handles it:

CREATE TRIGGER tr_update_post_count
AFTER INSERT OR DELETE ON post
FOR EACH ROW EXECUTE FUNCTION update_forum_post_count();

But something simpler is usually enough: a materialized view, if the counter may lag by minutes, or counting on the fly at read time, if there are not many rows.

Stored procedures

Stored procedures are PL/pgSQL code the application calls with CALL. Unlike regular PostgreSQL functions, procedures (starting with PostgreSQL 11) can do COMMIT and ROLLBACK inside themselves.

In most cases they are not needed: transaction management in application code covers almost everything.

When a procedure is justified:

  • Very heavy SQL operations, where every network round trip to the database costs — for example, processing millions of rows.
  • Moving data in batches: reads gigabytes, aggregates, writes with intermediate commits.
-- archiving in batches, with intermediate commits
CREATE PROCEDURE archive_old_orders() LANGUAGE plpgsql AS $$
DECLARE moved integer := 1;
BEGIN
    WHILE moved > 0 LOOP
        WITH batch AS (
            DELETE FROM order_doc WHERE ctid IN (
                SELECT ctid FROM order_doc
                WHERE created_at < now() - interval '1 year' LIMIT 10000)
            RETURNING *
        )
        INSERT INTO order_archive SELECT * FROM batch;
        GET DIAGNOSTICS moved = ROW_COUNT;
        COMMIT;
    END LOOP;
END;
$$;

The alternative is a scheduler in the application code: it is testable, visible in the repository, and not tied to PostgreSQL.

Performance: why FOR EACH ROW is dangerous at large volumes

On single-row edits a call per row goes unnoticed; on bulk ones the cost grows linearly: in INSERT INTO target SELECT * FROM source over a million rows the function runs a million times and slows the operation down several times over.

If a trigger is nevertheless needed on a table with bulk operations, use FOR EACH STATEMENT with a transition table: the function is called once and handles all affected rows in a single statement.

The second hidden danger is a deadlock. A trigger that touches a neighbouring table takes locks in its own order, while another transaction takes the same tables in the opposite one: PostgreSQL detects the cycle and aborts one of the transactions with an error.

How to find triggers in the database

What is already attached to the tables of a strange database:

-- all triggers on a specific table
SELECT trigger_name, event_manipulation, action_timing, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'order_doc';

-- the source code of the trigger function
SELECT prosrc FROM pg_proc WHERE proname = 'set_updated_at';

For debugging, add RAISE NOTICE 'fired: % on %', TG_OP, TG_TABLE_NAME; to the function — the message shows up in the psql console; NOTICE does not reach the server log by default.

In short

  • BEFORE fires before the write and can change the row, AFTER fires after; PostgreSQL ignores what the AFTER function returns.
  • FOR EACH ROW is called per row, FOR EACH STATEMENT once per statement, and it gets the rows through REFERENCING NEW TABLE.
  • Business logic belongs in application code: there it is tested, versioned, and visible.
  • updated_at via a trigger is a common mistake; the right way is an explicit updated_at = now() in the statement.
  • A trigger is justified for a change log that cannot be bypassed; the limits are DISABLE TRIGGER and TRUNCATE.
  • FOR EACH ROW over millions of rows slows the operation down several times; stored procedures are only for heavy batch work with intermediate commits.