Triggers and Stored Procedures in PostgreSQL

Triggers and stored procedures let you run logic inside the database, automatically on data changes or as callable routines. They're powerful, and they're the feature with the widest gap between "exactly right here" and "you'll regret this." Used for the narrow set of things the database should own, they're excellent. Used to hold business logic, they hide behavior in a place your application developers don't look and your version control barely tracks. This article covers both the mechanics and the judgment.

This is part 30 of the PostgreSQL Masterclass, following materialized views.

Functions: the building block

Both stored procedures and triggers are built on functions. A Postgres function is a routine, usually written in PL/pgSQL, that takes arguments and returns a value or a set of rows:

CREATE FUNCTION order_total(p_order_id bigint)
RETURNS numeric
LANGUAGE sql
AS $$
  SELECT sum(quantity * unit_price)
  FROM order_items WHERE order_id = p_order_id;
$$;

SELECT order_total(42);

Simple functions like this, especially in plain SQL, are genuinely useful and low-risk: they encapsulate a calculation and can be inlined by the planner. The judgment calls come with side-effecting procedures and triggers.

Triggers: logic that fires on data changes

A trigger runs a function automatically when a row is inserted, updated, or deleted. The function has access to the old and new versions of the row via OLD and NEW. The single best use of triggers is maintaining data that must stay consistent no matter which code path made the change.

The canonical good example is an updated_at timestamp:

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

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

Now updated_at is correct for every update, whether it came from the app, a migration, or a manual fix in psql. No application code can forget it, because the database owns it. That's the sweet spot for triggers: an invariant the database should guarantee regardless of who writes.

Other solid trigger uses:

• Audit logging: writing a row to an audit table on every change, so the trail can't be bypassed.
• Maintaining a denormalized value: keeping a comment_count on a post in sync as comments are inserted and deleted, when you've decided that denormalization is worth it.
• Enforcing a complex rule that a CHECK constraint can't express because it spans other rows or tables.

Where triggers hurt

The problem with triggers is that they're invisible. A developer runs a simple UPDATE and three other tables change, a notification fires, and a value recalculates, none of which is anywhere in the application code they're reading. Debugging "why did this row change" turns into archaeology.

So the anti-patterns:

• Business logic in triggers. Pricing rules, workflow transitions, and anything a product manager might change belong in application code where they're visible, testable, and versioned with the rest of the app.
• Triggers that call external systems. A trigger that makes an HTTP call or sends an email runs inside the transaction, so it holds locks during a slow network call and can't be rolled back if the transaction aborts after it. Use the outbox pattern instead: write an event row in the transaction and process it outside.
• Chains of triggers where one trigger's write fires another's, making the actual behavior of a single statement impossible to predict.

The rule I use: a trigger should maintain data integrity, not implement features. If it's enforcing "this must always be true about the data," it's probably fine. If it's implementing "when a user does X, the business wants Y," it belongs in the application.

Stored procedures vs functions

Postgres distinguishes functions from procedures (CREATE PROCEDURE, called with CALL). The practical difference is that a procedure can manage transactions, committing and rolling back inside itself, which a function can't. That makes procedures suited to batch and maintenance jobs that process data in chunks and commit as they go:

CREATE PROCEDURE purge_old_events()
LANGUAGE plpgsql AS $$
BEGIN
  LOOP
    DELETE FROM events WHERE created_at < now() - interval '1 year'
    AND id IN (SELECT id FROM events WHERE created_at < now() - interval '1 year' LIMIT 10000);
    EXIT WHEN NOT FOUND;
    COMMIT;   -- commit each batch so we don't hold a huge transaction
  END LOOP;
END;
$$;

That batched-delete-with-commit pattern is a legitimately good use of a procedure, because doing it from the application means many round trips, while the procedure runs it all database-side and keeps each transaction small.

The overall judgment

Logic in the database is a tool with a narrow right use. Favor it for: data integrity the database should own regardless of the writer (timestamps, audit trails, denormalized counters), and set-based batch jobs that are far cheaper run database-side. Avoid it for: business rules, workflow, and anything that talks to external systems, all of which belong in application code where they're visible and testable.

The failure mode isn't triggers or procedures themselves, it's putting the wrong things in them, where behavior hides from the people maintaining the app. Keep them for guarding data and doing bulk database work, keep features in the application, and both stay maintainable.

Next, we close the advanced-features module with row-level security, the feature that lets the database itself enforce which rows a tenant or user can see.