PostgreSQL Triggers and Stored Functions: The Double-Edged Sword of Data-Layer Logic

Anyone who has built a business system has probably encountered requirements like these: record an audit entry whenever an order is updated, refresh users.updated_at automatically, or process child records when a parent record is deleted. Implementing these in the application layer is verbose and easy to miss. A single entry point that forgets to invoke the logic can break consistency.

PostgreSQL offers another approach: triggers plus stored functions. By moving logic into the data layer, you let the database guarantee that it runs. Used well, they are powerful tools; used everywhere, they become a disaster for future maintainers. This article explains how to write them, when to use them, and when to avoid them entirely.

1. Distinguish Functions, Procedures, and Triggers

Many people discuss these three concepts interchangeably, but they operate at different levels.

Stored Function: A function written in PL/pgSQL, PL/Python, PL/V8, or another supported language that runs inside the database process and can be invoked from SQL like a built-in function.

CREATE OR REPLACE FUNCTION get_user_orders(p_user_id bigint)
RETURNS SETOF orders
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
    SELECT * FROM orders
    WHERE user_id = p_user_id
    ORDER BY created_at DESC;
END;
$$;

-- Call the function
SELECT * FROM get_user_orders(42);

Stored Procedure: PostgreSQL 11+ introduced PROCEDURE. Its biggest difference from a function is support for transaction control: COMMIT and ROLLBACK can be used inside a procedure. Procedures are invoked with CALL, while functions cannot manage their own transactions.

Trigger: A "hook" that automatically executes a function marked RETURNS trigger when an INSERT, UPDATE, or DELETE occurs on a table. The function does not run by itself; it must be attached with CREATE TRIGGER before it can fire.

-- Trigger function: fixed signature returning trigger
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

-- Attach it to the table
CREATE TRIGGER trg_users_set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

Remember this: functions are called; triggers are fired. The former are explicit, while the latter are implicit. That distinction drives every tradeoff discussed below.

2. The Four Dimensions of a Trigger

You only need four dimensions to understand how a trigger behaves.

1. Timing: BEFORE vs. AFTER

• BEFORE: Runs before the row is actually written. It can modify NEW or return NULL to skip the row. It is suitable for data normalization, validation, and calculating default values.
• AFTER: Runs after the row has already been written. It cannot modify the data and is suitable for side effects, such as writing audit records, sending notifications, or synchronizing denormalized fields.

The most common beginner mistake is modifying NEW.updated_at in an AFTER trigger. The change has no effect because the row has already been written.

2. Granularity: Row-Level vs. Statement-Level

• FOR EACH ROW: Runs once for every affected row. An INSERT of 1,000 rows runs it 1,000 times.
• FOR EACH STATEMENT: Runs only once after the entire statement, regardless of how many rows it affects. It is suitable for maintaining table-wide aggregates.

CREATE TRIGGER trg_recount_tags
  AFTER INSERT OR UPDATE OR DELETE ON articles
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_tag_counts();

3. Event: INSERT / UPDATE / DELETE / TRUNCATE

Events can be combined. You can also use UPDATE OF col1, col2 so the trigger fires only when specific columns change, preventing unrelated updates from invoking it.

4. Condition: WHEN

CREATE TRIGGER trg_log_price_change
  AFTER UPDATE OF price ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

WHEN filters at the trigger level. It short-circuits one step earlier than an IF inside the function body and saves CPU time.

3. Four Scenarios Where Triggers Are Worth Using

Calling triggers a "double-edged sword" does not mean you should never use them. It means using them where they belong. The following four scenarios are their natural territory.

Scenario 1: Audit Logs

When the business application writes audit records, N entry points require the same logic N times, and missing just one leaves a gap. Put the logic in the database and no write path can bypass it.

CREATE OR REPLACE FUNCTION audit_user_change()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO user_audit(user_id, action, old_data, new_data, changed_at, changed_by)
  VALUES(
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END,
    now(),
    current_setting('app.current_user_id', true)
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

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

Pair current_setting('app.current_user_id', true) with SET LOCAL app.current_user_id = ... in the application layer to pass in who performed the action. This is the standard pattern for data-layer auditing.

Scenario 2: Automatic updated_at Values

The earlier example already demonstrated this. Mechanical operations that must always run and do not alter business semantics are the ideal use case for triggers.

Scenario 3: Cascading Data Maintenance

This does not mean foreign-key cascades, which PostgreSQL supports natively. It means maintaining denormalized or cached fields in table B whenever table A changes.

-- Refresh the user's denormalized "most recent order time" field when an order's status changes
CREATE OR REPLACE FUNCTION touch_user_last_order()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE users
  SET last_order_at = now()
  WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$;

This saves an application-layer round trip and cannot fail merely because the application forgot to invoke the update.

Scenario 4: Complex Constraints That CHECK Cannot Express

A CHECK constraint can only operate on a single row and must use an immutable expression. CHECK cannot handle the following cases, but a trigger can:

• "A user cannot have more than five incomplete orders" (a cross-row constraint)
• "A time range cannot overlap an existing record" (this can also use an exclusion constraint with EXCLUDE plus GiST, but triggers are more straightforward for more complex logic)
• "The state machine may only transition from pending to paid; it cannot skip states"

CREATE OR REPLACE FUNCTION check_order_status_transition()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.status = 'pending' AND NEW.status NOT IN ('paid','cancelled') THEN
    RAISE EXCEPTION 'Invalid state transition: % -> %', OLD.status, NEW.status;
  END IF;
  RETURN NEW;
END;
$$;

These constraints concern data integrity. Should they live in the database or the application layer? The database is the better location because every writer must obey them.

4. Cases Where You Should Never Use Triggers

Conversely, I have seen countless teams run into problems in the following scenarios.

Core business logic. Calculating order totals, combining promotions, or applying risk-control rules inside triggers is a disaster. The reasons are:

1. Not testable: You cannot write a unit test for a trigger, while integration tests are slow and brittle.
2. Not versionable: Database function definitions are scattered across migration scripts, creating a separate version of business logic from the one in the application code, with no clear correspondence between them.
3. Not visible: A new developer runs an UPDATE on a row and sees its amount change, with no idea which trigger calculated it.

Cross-service logic. In a microservice architecture, attaching a trigger to service A's table to synchronize service B tears apart the service boundary. If service B goes down, the trigger must either swallow the exception and leave inconsistent data or raise the exception and cause service A's write to fail. Neither outcome is acceptable.

External calls. Do not perform http_get calls, RPCs, send emails, or write to message queues from triggers, even if you have installed extensions such as plughog, pg_cron, or pgsql-http. Database transactions are valuable. If an external call becomes slow, locks accumulate and the entire database can collapse.

One firm rule: a trigger should perform only synchronous, rollback-safe work inside the database. Once the logic crosses a process boundary, it belongs back in the application layer.

5. The Double-Edged Sword: Why Too Many Triggers Become Unmanageable

The fundamental problem with triggers is implicit execution. The code UPDATE users SET name = ... appears to change only one field, but it may actually fire five triggers, each of which may fire others. A change to users causes audit to write to audit_log, and audit_log has another trigger of its own.

The consequences include:

• Behavior is difficult to trace: A production record changes unexpectedly, but nothing appears in the application logs because the change never passed through the application.
• Debugging is difficult: Running \d+ users in psql does not tell you what the triggers actually do. You must inspect each function definition individually. EXPLAIN also does not tell you how much time the triggers consumed.
• Performance blind spots: Row-level triggers multiply their cost by N during bulk operations. An INSERT of 1 million rows with a row-level trigger means 1 million function calls. Many teams discover this only in production.
• Migration is difficult: Whether you replace the ORM, change languages, or shard the database, triggers remain invisible dependencies. Move the data without them and the logic disappears.

I once established this team rule: every production trigger must be documented in a table comment or a dedicated TRIGGERS.md file, explaining what it does, why it cannot live in the application layer, and who owns it. During Code Review, any new trigger is challenged by default and rejected if it cannot justify its existence.

6. Stored Functions vs. the Application Layer: A Familiar Debate

Stored functions are not quite the same as triggers. Stored functions are called explicitly, so they do not suffer from the original sin of implicit execution. They do, however, have their own tradeoffs.

Where functions are faster:

• No network round trips: Complex reports can be calculated close to the data, returning only the result. Pulling tens of millions of rows into the application for aggregation is unrealistic.
• Reusable by any client: BI tools, psql, and services written in different languages can all call SELECT my_report() directly.
• Transactional consistency is inherent because the logic runs inside the database.

Where functions hurt:

• A narrow language ecosystem: PL/pgSQL's tooling, dependency management, and testing frameworks lag far behind mainstream languages.
• Difficult to review and refactor: Many teams construct function definitions as unwieldy SQL strings, with poor IDE support.
• Database lock-in: PostgreSQL version changes require care, and migrating to a non-PostgreSQL database generally requires a complete rewrite.

My practical judgment is:

• Reports, ETL, and high-volume computations that are performance bottlenecks: Use stored functions; they are worth it.
• Business logic and changing domain rules: Keep them in the application layer. Even if it is slightly slower, controllability matters far more than performance.
• Anything in between: Prefer the application layer and move the logic down only after proving that round trips are the actual bottleneck.

One concrete signal is when application-layer logic takes 30 seconds primarily because it retrieves 1 million rows. That is a strong indication that a stored function may help. In other cases, default to the application layer.

Summary

• Triggers and functions place logic in the data layer. Their core value is that every write path must execute the logic, something the application layer cannot guarantee.
• Use BEFORE to modify data and AFTER for side effects; row-level triggers run per row, statement-level triggers run per statement, and WHEN filters early. Understand how these four dimensions combine before implementing a trigger.
• Four appropriate trigger use cases are audit logs, automatic updated_at values, cascading maintenance of denormalized data, and complex integrity constraints.
• Three categories to avoid entirely are core business logic, cross-service boundaries, and external calls. Once the logic crosses a process boundary, move it back to the application layer.
• Prefer stored functions for reports and ETL, while keeping business logic in the application layer by default. Controllability is always worth more than a small reduction in round-trip overhead.

A trigger is not an optimization tool; it is a constraint tool. Use it when "missing one path breaks everything," and avoid it when "invisible behavior becomes uncontrollable."