丁久

Posted on May 17 • Originally published at dingjiu1989-hue.github.io

Database Triggers: Use Cases, Performance Costs, and Alternatives

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

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

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Database Triggers: Use Cases, Performance Costs, and Alternatives

Database Triggers: Use Cases, Performance Costs, and Alternatives