Database Triggers: Use Cases, Performance Costs, and Alternatives

Database Triggers: Use Cases, Performance Costs, and Alternatives

A trigger is a named database object that executes a function automatically in response to INSERT, UPDATE, DELETE, or TRUNCATE events on a table. Triggers run inside the same transaction and offer powerful guarantees, but they carry real costs.

Anatomy of a Trigger

A trigger consists of two parts: the trigger definition and the trigger function. PostgreSQL separates them, allowing one function to serve multiple triggers.

CREATE OR REPLACE FUNCTION log_changes()

RETURNS TRIGGER AS $$

BEGIN

IF TG_OP = 'UPDATE' THEN

INSERT INTO audit_log (table_name, row_id, old_data, new_data, changed_at)

VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD), row_to_json(NEW), NOW());

RETURN NEW;

ELSIF TG_OP = 'DELETE' THEN

INSERT INTO audit_log (table_name, row_id, old_data, changed_at)

VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD), NOW());

RETURN OLD;

END IF;

RETURN NULL; -- for INSERT, do nothing

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users

AFTER UPDATE OR DELETE ON users

FOR EACH ROW EXECUTE FUNCTION log_changes();

Trigger timing options:

  • BEFORE: Runs before the operation. Useful for validation or default-value injection.

  • AFTER: Runs after the operation. Used for audit logs, cascade updates, or synchronization.

  • INSTEAD OF: Replaces the operation entirely. Only valid on views.

Common Use Cases

Audit Logging

Recording every change to sensitive tables is the most common trigger use case:

CREATE TABLE audit_log (

id BIGSERIAL PRIMARY KEY,

table_name TEXT NOT NULL,

operation TEXT NOT NULL,

row_id INTEGER,

old_values JSONB,

new_values JSONB,

changed_by TEXT DEFAULT current_user,

changed_at TIMESTAMPTZ DEFAULT NOW()

);

CREATE OR REPLACE FUNCTION audit_employee_changes()

RETURNS TRIGGER AS $$

BEGIN

INSERT INTO audit_log (table_name, operation, row_id, old_values, new_values)

VALUES ('employees', TG_OP, COALESCE(NEW.id, OLD.id),

CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD)::jsonb END,

CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW)::jsonb END);

RETURN COALESCE(NEW, OLD);

END;

$$ LANGUAGE plpgsql SECURITY DEFINER;

Business Rule Validation

BEFORE triggers enforce invariants that cannot be expressed as CHECK constraints:

CREATE OR REPLACE FUNCTION validate_order()

RETURNS TRIGGER AS $$

BEGIN

IF NEW.total < 0 THEN

RAISE EXCEPTION 'Order total cannot be negative: %', NEW.total;

END IF;

IF NEW.status = 'shipped' AND OLD.status != 'paid' THEN

RAISE EXCEPTION 'Cannot ship unpaid order %', NEW.id;

END IF;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

Cross-Table Synchronization

Keep denormalized counters or summary tables in sync:

CREATE OR REPLACE FUNCTION update_user_order_count()

RETURNS TRIGGER AS $$

BEGIN

IF TG_OP = 'INSERT' THEN

UPDATE users SET order_count = order_count + 1 WHERE id = NEW.user_id;

ELSIF TG_OP = 'DELETE' THEN

UPDATE users SET order_count = order_count - 1 WHERE id = OLD.user_id;

END IF;

RETURN COALESCE(NEW, OLD);

END;

$$ LANGUAGE plpgsql;

Performance Costs

Triggers add overhead that is easy to underestimate:

  • Per-row execution : FOR EACH ROW triggers execute the function once per affected row. An UPDATE that modifies 100,000 rows runs the trigger 100,000 times.

  • Transaction scope : Trigger failures roll back the entire operation, not just the trigger action.

  • Nested triggers : A trigger that updates another table can fire triggers on that table, creating a cascade that is difficult to debug.

  • Lock duration : Triggers extend the time a row or page lock is held, increasing contention in high-concurrency workloads.

The pg_stat_user_functions view helps identify trigger overhead:

SELECT total_time / calls AS avg_time_per_call,

calls,

funcname

FROM pg_stat_user_functions

WHERE funcname LIKE '%trigger%'

ORDER BY total_time DESC;

Debugging Challenges

Triggers execute transparently. Developers new to a codebase often discover triggers only when an UPDATE suddenly fails with an unexpected error. Mitigation strategies:

  • Naming conventions : Prefix trigger functions with trg_ and trigger names with the table name.

2\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\. Document dependencies : Maintain a trigger dependency map. 3\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\. Session-level disable (requires superuser or explicit privilege):

SET session_replication_role = replica;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Perform bulk operation

SET session_replication_role = origin;

Alternatives: Change Data Capture (CDC)

When triggers become too expensive or complex, Change Data Capture offers a streaming alternative:

  • Logical replication (PostgreSQL native): Streams changes to a consumer without triggers.

  • Debezium : Captures row changes via the write-ahead log and publishes them to Kafka.

  • pgoutput + pg_recvlogical : Custom CDC implementations.

CDC avoids trigger overhead, does not slow the original transaction, and supports real-time streaming to external systems.

Best Practices

  • Prefer CONSTRAINT triggers for deferred validation when possible.

  • Keep trigger functions fast: avoid network calls, file I/O, and expensive computations.

  • Use FOR EACH STATEMENT when row-level granularity is unnecessary.

  • Add comments explaining why the trigger exists, not just what it does.

  • Test trigger behavior with rollback test cases.

Triggers are a legitimate tool for data integrity, but they should be your last resort, not your first instinct. When a CHECK constraint, UNIQUE index, or FOREIGN KEY can enforce the rule, use that instead.