← Back to the section

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

How a trigger works

Imagine: every time someone changes a row in a table, PostgreSQL automatically calls a piece of PL/pgSQL code. That is a trigger.

A minimal trigger consists of two parts: a function and the trigger itself, which calls that function.

-- 1. 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 NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. The trigger binding the function to a table and an 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.
  • AFTER — after the write. The function sees the already committed data, but it can no longer change 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.

Inside the trigger function you have access to special variables: TG_OP (what happened: INSERT, UPDATE or DELETE), TG_TABLE_NAME (the table name), NEW (the new row values), OLD (the old values).

Why business logic in triggers is a bad idea

It seems convenient to keep logic in the database: it will always run, even if someone runs an UPDATE straight from psql. But in practice it turns into a headache.

Logic in a trigger is invisible. A developer writes UPDATE order_doc SET status = 'paid' and does not know that at that moment five more functions quietly run. Debugging such code is painful.

Triggers are hard to test. You cannot run a unit test without a real database. You need integration tests with Testcontainers or a full PostgreSQL — that is slower and heavier.

Versioning is awkward. The application code and the trigger live in different places. When rolling back a release, you have to roll back the migration with the trigger in sync — that is a separate operation, easy to forget about.

Coupling to PostgreSQL. Logic in PL/pgSQL will not port 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. This is hidden behavior. When something goes wrong, finding the cause is not easy.

The right way: set DEFAULT now() in the schema and explicitly specify updated_at = now() in the application code.

-- Schema
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()
);

-- Query 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_total_positive CHECK (total_amount >= 0);

If the constraint is more complex (for example, "at any given moment no more than one doctor is on duty in the department"), it is better to implement the check in the application code with SELECT FOR UPDATE than to write a trigger. That way the logic is easier to understand and test.

Trigger chains

When one trigger calls another, which calls a third — that is a guaranteed nightmare when debugging. It is better to replace such a chain with an explicit event handler in the application code.

Notifications and HTTP calls from a trigger

NOTIFY from a trigger is a fragile construct. If the receiver is not listening, the message is lost. Instead, people use the Outbox pattern: the trigger (or the application code) writes an event into a special table, and a separate process delivers it further on.

When a trigger is justified

There are a few situations where a trigger really helps.

Audit trail for compliance

If your product runs in a bank, a medical institution, or another regulated industry, you need to record every change to the data. A trigger guarantees that no change slips past the log, even if a developer ran an UPDATE manually in psql.

CREATE TRIGGER tr_order_doc_audit
AFTER INSERT OR UPDATE OR DELETE ON order_doc
FOR EACH ROW EXECUTE FUNCTION log_to_audit();

The alternative — outbox plus application code — is more flexible, but it requires discipline: every developer must remember to create a log entry. A trigger takes that responsibility off the team.

The database as the source of truth for several applications

If several applications written in different languages connect to a single PostgreSQL database, moving the shared logic into a trigger is reasonable. It is the only place where it is guaranteed to run, no matter 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 more often simpler alternatives are enough:

  • Materialized view — refreshed on a schedule, suitable when the data can be slightly stale.
  • Counting on the fly at read time — if there are not many records.
  • Read model via an event handler in the code — flexible and testable.

Stored procedures

Stored procedures are PL/pgSQL functions that are called explicitly from the application. 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 the application code covers almost all scenarios.

When a procedure is justified:

  • Very heavy SQL operations, where each network round-trip is costly — for example, processing millions of rows.
  • ETL: reads gigabytes, aggregates, writes in batches with intermediate commits.
-- Archiving old orders in batches, with intermediate commits
CREATE PROCEDURE archive_old_orders() LANGUAGE plpgsql AS $$
DECLARE rows_affected integer := 1;
BEGIN
    WHILE rows_affected > 0 LOOP
        WITH deleted AS (
            DELETE FROM order_doc
            WHERE created_at < now() - interval '1 year'
            RETURNING *
        )
        INSERT INTO order_archive SELECT * FROM deleted;
        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        COMMIT;
    END LOOP;
END;
$$;

For scheduled tasks the alternative is a scheduler in the application code with batch logic. It is testable, visible in the repository, and not tied to PostgreSQL.

Performance: why FOR EACH ROW is dangerous at large volumes

FOR EACH ROW is literally one function call per affected row. For ordinary operations this goes unnoticed. But for bulk inserts the cost rises sharply.

INSERT INTO target SELECT * FROM source — if source has a million rows, a FOR EACH ROW trigger will fire a million times. This can slow the operation down several times over.

If a trigger is nevertheless needed on a table with bulk operations, use FOR EACH STATEMENT: the function is called once for the whole statement.

Another hidden danger: a deadlock. If the trigger updates another table, the order in which locks are acquired may conflict with other transactions that access the same tables in a different order. PostgreSQL will detect the deadlock and abort one of the transactions with an error.

How to find triggers in the database

If you have joined a new project and want to understand which triggers already exist:

-- 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 you can add RAISE NOTICE right into the trigger function — the message will appear in the logs:

CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'TRIGGER fired: % on table %', TG_OP, TG_TABLE_NAME;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

In short

  • A trigger is code in the database that runs automatically on INSERT, UPDATE or DELETE.
  • BEFORE fires before the write and can modify the data, AFTER fires after.
  • FOR EACH ROW is called for each row; FOR EACH STATEMENT — once per statement.
  • 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 query.
  • A trigger is justified for compliance logging, when every change absolutely must be recorded.
  • FOR EACH ROW on bulk operations (millions of rows) slows things down substantially.
  • Stored procedures are useful for heavy ETL with intermediate commits; in other cases application code is enough.
  • Indexes in PostgreSQL — how to speed up queries without extra logic in the database.
  • VACUUM and bloat — how PostgreSQL clears out the "garbage" left after UPDATE and DELETE.
  • Table partitioning — how to split a large table into parts.