How to Create Triggers in PostgreSQL

Triggers are one of those PostgreSQL features I underestimated for a long time — until I inherited a production database where half the “business logic” was actually living quietly inside trigger functions, enforcing rules that weren’t documented anywhere in the application code. Once I understood how they worked, I started using them deliberately: to keep audit logs consistent, enforce data integrity rules that go beyond constraints, and automatically maintain derived columns without relying on the application layer to remember to do it every single time.

In this article, I’ll walk through what triggers are, the different types available, the exact syntax for creating them, several practical real-world examples, common troubleshooting scenarios, and the best practices I follow to avoid the classic pitfalls of “invisible” trigger-based logic.

What Is a Trigger?

A trigger is a database object that automatically executes a specified function whenever a particular event happens on a table or view — an INSERT, UPDATE, DELETE, or TRUNCATE. Triggers let me enforce rules and automate actions at the data layer itself, so the behavior applies consistently no matter what application, script, or user is modifying the data — as opposed to logic living only in application code, which can be bypassed by a direct SQL script, a different application, or a manual fix in production.

A trigger consists of two parts:

1. The trigger function — usually written in PL/pgSQL, containing the actual logic to run.
2. The trigger definition — created with CREATE TRIGGER, specifying which table, which events, and when (before, after, or instead of) the function should fire.

Trigger Timing and Levels

PostgreSQL triggers can fire at different points and different granularities:

• BEFORE — runs before the triggering event, and can modify the row being inserted/updated, or prevent the operation entirely by raising an exception.
• AFTER — runs after the event has completed, typically used for logging, cascading updates to other tables, or notifications.
• INSTEAD OF — used exclusively on views, replacing the default behavior entirely, which is how updatable views are often implemented for complex view definitions.
• FOR EACH ROW — the trigger function runs once per affected row.
• FOR EACH STATEMENT — the trigger function runs once per SQL statement, regardless of how many rows were affected.

Basic Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | TRUNCATE}
ON table_name
[FOR EACH ROW | FOR EACH STATEMENT]
[WHEN (condition)]
EXECUTE FUNCTION trigger_function_name();

The trigger function itself must return type trigger and is defined separately using CREATE FUNCTION.

Example 1: Automatically Setting an updated_at Timestamp

This is probably the single most common trigger pattern I write in nearly every project:

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

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

UPDATE orders SET status = 'shipped' WHERE id = 42;
SELECT updated_at FROM orders WHERE id = 42;
-- reflects the current timestamp automatically

Example 2: Data Validation Before Insert

CREATE OR REPLACE FUNCTION validate_order_amount()
RETURNS trigger
AS $$
BEGIN
    IF NEW.amount <= 0 THEN
        RAISE EXCEPTION 'Order amount must be positive, got %', NEW.amount;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_order_amount
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order_amount();

INSERT INTO orders (amount) VALUES (-10);
-- ERROR: Order amount must be positive, got -10

This is a good example of logic that could theoretically live in a CHECK constraint for a simple case like this, but for more complex, multi-column, or cross-table validation rules, a trigger is often the only practical option.

Example 3: Audit Logging with an AFTER Trigger

CREATE TABLE orders_audit (
    id SERIAL PRIMARY KEY,
    order_id INTEGER,
    changed_at TIMESTAMP DEFAULT now(),
    old_status TEXT,
    new_status TEXT,
    changed_by TEXT
);

CREATE OR REPLACE FUNCTION log_order_status_change()
RETURNS trigger
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO orders_audit (order_id, old_status, new_status, changed_by)
        VALUES (NEW.id, OLD.status, NEW.status, current_user);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_order_status_change
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_order_status_change();

Using IS DISTINCT FROM here instead of != is deliberate — it correctly handles the case where either value is NULL, which a plain != comparison would otherwise evaluate as NULL (and therefore skip) instead of true.

Example 4: Maintaining a Denormalized Summary Column

Triggers are a common way to keep a denormalized aggregate value in sync without recalculating it on every read:

CREATE OR REPLACE FUNCTION update_customer_order_count()
RETURNS trigger
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE customers SET order_count = order_count + 1 WHERE id = NEW.customer_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE customers SET order_count = order_count - 1 WHERE id = OLD.customer_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_customer_order_count
AFTER INSERT OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_customer_order_count();

Notice this trigger returns NULL — that’s intentional and correct for AFTER row-level triggers, since the return value of an AFTER trigger is ignored by PostgreSQL anyway; only BEFORE triggers use the returned row to determine what actually gets written.

The special variable TG_OP tells me which operation (INSERT, UPDATE, DELETE, or TRUNCATE) triggered the function, which is essential for a single trigger function shared across multiple event types, as shown here.

Example 5: Using WHEN to Limit When a Trigger Fires

CREATE TRIGGER trg_log_status_change_only
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_order_status_change();

Adding a WHEN clause directly in the trigger definition means the trigger function isn’t even invoked unless the condition is true, which is more efficient than checking the condition inside the function body for every single update.

Statement-Level Triggers

For cases where I don’t need per-row granularity — for example, refreshing a materialized view after any bulk change to a table — FOR EACH STATEMENT avoids the overhead of firing once per row:

CREATE OR REPLACE FUNCTION refresh_order_summary()
RETURNS trigger
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_refresh_order_summary
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_order_summary();

INSTEAD OF Triggers on Views

CREATE VIEW active_customers AS
SELECT id, name, email FROM customers WHERE is_active = true;

CREATE OR REPLACE FUNCTION insert_active_customer()
RETURNS trigger
AS $$
BEGIN
    INSERT INTO customers (name, email, is_active)
    VALUES (NEW.name, NEW.email, true);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_insert_active_customer
INSTEAD OF INSERT ON active_customers
FOR EACH ROW
EXECUTE FUNCTION insert_active_customer();

INSERT INTO active_customers (name, email) VALUES ('Ali Raza', 'ali@example.com');

This works even though active_customers is a view with a WHERE filter, which wouldn’t normally be directly insertable without this trigger telling PostgreSQL exactly how to translate the insert.

Common Use Cases

• Audit trails — recording who changed what and when, often into a dedicated history table.
• Data normalization and validation — enforcing formatting rules or business constraints that go beyond simple CHECK constraints.
• Maintaining denormalized counters or summary columns for performance-sensitive read paths.
• Cascading side effects — such as updating related records or inserting into a notification queue table.
• Enforcing referential integrity beyond foreign keys — for cases involving conditional logic that a plain foreign key constraint can’t express.
• Making views updatable via INSTEAD OF triggers.

Troubleshooting Common Issues

Trigger doesn’t seem to fire at all I check that the trigger is actually enabled — triggers can be disabled with ALTER TABLE ... DISABLE TRIGGER trigger_name, which is easy to forget about after a maintenance operation. I also confirm the event type (INSERT/UPDATE/DELETE) in the CREATE TRIGGER statement actually matches the operation I’m testing.

Infinite recursion / trigger firing itself repeatedly This happens when a trigger function on table A updates table A again inside itself (directly, or indirectly through another trigger). I either restructure the logic to avoid the self-referential update, or use a WHEN clause / conditional check inside the function to prevent re-triggering when nothing meaningful has actually changed.

Silent failures where changes to NEW aren’t applied For BEFORE row-level triggers, if the function doesn’t explicitly RETURN NEW (with modifications applied), any changes made to the NEW record inside the function body are silently discarded. I always double-check the final RETURN statement in every BEFORE trigger function.

Performance degradation on bulk inserts/updates Row-level triggers fire once per affected row, so a bulk INSERT of a hundred thousand rows means a hundred thousand trigger function executions. For bulk operations where per-row logic isn’t strictly necessary, I consider a statement-level trigger instead, or temporarily disabling the trigger during a large, controlled data migration (re-enabling it immediately afterward, and manually reconciling any logic the trigger would have applied).

Trigger execution order confusion with multiple triggers on the same event When multiple triggers are defined for the same table and event, PostgreSQL fires them in alphabetical order by trigger name — not creation order. This trips people up often. I name triggers with an explicit numeric or alphabetical prefix (like trg_01_validate, trg_02_audit) when execution order actually matters.

Best Practices

• Keep trigger logic simple and predictable. A trigger that quietly does five different things is a maintenance hazard — I try to keep each trigger focused on one clear responsibility.
• Document triggers prominently. Because triggers execute invisibly from the perspective of whatever INSERT/UPDATE/DELETE statement caused them, I make sure their existence and purpose are documented somewhere obvious — in the schema migration file, and ideally in a comment using COMMENT ON TRIGGER.
• Use WHEN clauses to avoid unnecessary function calls. This is both a performance and clarity win over checking the same condition inside the function body.
• Be deliberate about BEFORE vs. AFTER. Use BEFORE when I need to validate or modify the row itself; use AFTER for side effects like logging or cascading updates that shouldn’t block or alter the original operation.
• Avoid long-running operations inside row-level triggers. A trigger that makes an external API call or performs an expensive calculation on every single row insert can tank bulk-operation performance dramatically.
• Watch for recursive trigger chains. When a trigger’s side effect could plausibly cause another trigger to fire (on the same or a different table), I map out that chain explicitly to avoid infinite loops or unexpected cascading behavior.
• Test trigger behavior under bulk operations, not just single-row cases. A trigger that works perfectly for one INSERT at a time can behave very differently — or perform poorly — under a bulk load of thousands of rows.
• Version control trigger and function definitions together. Since a trigger is only half the picture without its underlying function, I keep both in the same migration file so they’re always deployed and reviewed as a unit.

Managing Triggers: Disabling, Enabling, and Dropping

Triggers can be temporarily disabled without dropping them entirely, which is useful during bulk data loads or migrations where I want to bypass validation or audit logic temporarily:

ALTER TABLE orders DISABLE TRIGGER trg_validate_order_amount;

-- perform bulk operation here

ALTER TABLE orders ENABLE TRIGGER trg_validate_order_amount;

I can also disable every trigger on a table at once:

ALTER TABLE orders DISABLE TRIGGER ALL;

I use this sparingly and always re-enable triggers immediately afterward within the same maintenance window, since leaving triggers disabled unintentionally is a classic way to silently lose audit trails or data validation for an unknown period of time.

To remove a trigger entirely:

DROP TRIGGER IF EXISTS trg_validate_order_amount ON orders;

Inspecting Existing Triggers

Before adding a new trigger, I always check what’s already defined on a table, since stacking multiple triggers with overlapping responsibility is a common source of confusion:

\d orders

Or querying the catalog directly for more detail, including whether a trigger is currently enabled:

SELECT tgname, tgenabled, pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal;

The tgisinternal filter is important — it excludes triggers PostgreSQL creates automatically to enforce foreign key constraints, which otherwise clutter the result set with implementation details I don’t usually need to see.

Real-World Example: Soft Deletes via Trigger

A pattern I’ve implemented several times is converting a DELETE into a soft delete (setting a flag instead of physically removing the row) transparently, so application code doesn’t need to be rewritten:

ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMP;

CREATE OR REPLACE FUNCTION soft_delete_customer()
RETURNS trigger
AS $$
BEGIN
    UPDATE customers SET deleted_at = now() WHERE id = OLD.id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_soft_delete_customer
BEFORE DELETE ON customers
FOR EACH ROW
EXECUTE FUNCTION soft_delete_customer();

Returning NULL from a BEFORE DELETE row-level trigger cancels the actual deletion, so the row remains in the table with deleted_at populated instead of being physically removed. I combine this with a view (SELECT * FROM customers WHERE deleted_at IS NULL) so application queries only see “active” rows by default, without needing to rewrite every query with an explicit filter.

Frequently Asked Questions

Can a single trigger function be reused across multiple tables? Yes, as long as the logic doesn’t reference table-specific column names that don’t exist on every table it’s attached to. The generic set_updated_at() function shown earlier is a good example — it works identically on any table with an updated_at column.

What’s the difference between TG_TABLE_NAME and hardcoding a table name inside a trigger function? TG_TABLE_NAME (along with TG_OP, TG_WHEN, and other special variables) lets a single generic trigger function introspect which table and event triggered it, which is essential for building reusable trigger functions shared across many tables without duplicating nearly identical code.

Do triggers fire during COPY operations? Yes — row-level triggers fire normally during COPY just as they would during INSERT, which is worth remembering since bulk COPY operations on large files can trigger a very large number of individual row-level trigger executions if not accounted for.

When to Reach for Application Logic Instead

Not every piece of “reactive” logic belongs in a trigger. If a rule only needs to apply from one specific application entry point, and there’s no risk of the data being modified through any other path, keeping that logic in application code is often simpler to test, deploy, and reason about than a trigger hidden inside the schema. I reserve triggers specifically for rules that need to hold true universally, regardless of which system or script touches the data — that’s the property triggers are genuinely good at guaranteeing, and it’s not something worth reaching for reflexively when a simpler application-level check would do.

Final Thoughts

Triggers are an extremely powerful way to enforce consistency and automate side effects directly at the data layer, but that power comes with a real maintainability cost if they’re overused or poorly documented — I’ve personally lost hours tracking down “mystery” behavior that turned out to be a forgotten trigger from years earlier. My rule of thumb is to use triggers for logic that genuinely needs to apply universally regardless of which application or script touches the data — audit trails, data integrity rules, and denormalized aggregate maintenance are the classic, well-justified cases — and to keep the trigger functions themselves as small, well-documented, and predictable as possible.