Offensive Security Researcher & Quantum Cryptography Analyst

How to Use Triggers in PostgreSQL

Abdul Wahab Junaid
How to Use Triggers in PostgreSQL

Triggers are one of those PostgreSQL features I was hesitant about for a long time — they felt like hidden magic, logic that runs behind the scenes without an obvious call site in the application code. And honestly, that caution is fair; overusing triggers can make a system hard to reason about. But used deliberately, for things like auditing, enforcing data integrity that can’t be expressed as a simple constraint, or keeping denormalized data in sync, triggers are genuinely one of the most powerful tools PostgreSQL gives you. Let me walk through how they actually work.

What Is a Trigger in PostgreSQL?

A trigger is a piece of logic that automatically executes in response to a specific event on a table or view — INSERT, UPDATE, DELETE, or TRUNCATE. Triggers in PostgreSQL are built from two separate pieces:

  1. A trigger function — written in PL/pgSQL (or another procedural language) that contains the actual logic.
  2. A trigger definition — which tells PostgreSQL when to call that function (before or after which event, on which table).

This two-step structure is different from some other databases where the trigger body is defined inline, and it’s worth getting comfortable with because it means the same trigger function can be reused across multiple triggers.

Basic Syntax

Step 1 — create the trigger function:

CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- logic here
    RETURN NEW; -- or OLD, or NULL depending on context
END;
$$ LANGUAGE plpgsql;

Step 2 — attach it to a table:

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

Understanding BEFORE vs AFTER vs INSTEAD OF

Understanding FOR EACH ROW vs FOR EACH STATEMENT

Example 1: Auditing Changes

One of the most common uses of triggers is maintaining an audit log:

CREATE TABLE employee_audit (
    audit_id SERIAL PRIMARY KEY,
    employee_id INT,
    changed_by TEXT,
    change_type TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_audit(employee_id, changed_by, change_type, old_data, new_data)
        VALUES (OLD.id, current_user, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employee_audit(employee_id, changed_by, change_type, old_data)
        VALUES (OLD.id, current_user, 'DELETE', to_jsonb(OLD));
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO employee_audit(employee_id, changed_by, change_type, new_data)
        VALUES (NEW.id, current_user, 'INSERT', to_jsonb(NEW));
    END IF;
    RETURN NULL; -- return value ignored for AFTER triggers
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION log_employee_changes();

Notice the special variable TG_OP, which tells you which operation fired the trigger, and to_jsonb(), which is a convenient way to capture the entire row as JSON for an audit trail without listing every column individually.

Example 2: Automatically Updating a Timestamp

A very common pattern — automatically setting an updated_at column whenever a row changes:

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

CREATE TRIGGER employees_set_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

This has to be a BEFORE trigger, since it needs to modify NEW before the row is actually written.

Example 3: Enforcing Business Rules

Triggers are useful when a business rule can’t be expressed cleanly as a CHECK constraint because it needs to look at other rows or tables:

CREATE OR REPLACE FUNCTION prevent_salary_decrease()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < OLD.salary THEN
        RAISE EXCEPTION 'Salary cannot be decreased. Old: %, New: %', OLD.salary, NEW.salary;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION prevent_salary_decrease();

If this trigger raises an exception, the entire transaction that triggered it is rolled back.

Example 4: Keeping Denormalized Data in Sync

CREATE OR REPLACE FUNCTION update_department_headcount()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE departments SET headcount = headcount + 1 WHERE id = NEW.department_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE departments SET headcount = headcount - 1 WHERE id = OLD.department_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_headcount
AFTER INSERT OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION update_department_headcount();

Conditional Triggers with WHEN

You can attach a WHEN clause so the trigger function only runs when a specific condition is true, avoiding unnecessary function calls:

CREATE TRIGGER notify_large_raise
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.2)
EXECUTE FUNCTION notify_hr();

This is more efficient than checking the condition inside the function itself, since PostgreSQL evaluates WHEN before even invoking the function.

Disabling and Enabling Triggers

Sometimes you need to temporarily disable a trigger — for a bulk data migration, for example, where you don’t want audit logs or business rule checks firing for every row:

ALTER TABLE employees DISABLE TRIGGER employee_audit_trigger;
-- do your bulk operation
ALTER TABLE employees ENABLE TRIGGER employee_audit_trigger;

Or disable all triggers on a table at once:

ALTER TABLE employees DISABLE TRIGGER ALL;

Be cautious with this — disabling triggers means any integrity checks or audit logging they perform simply won’t happen during that window.

Viewing Existing Triggers

\dS+ employees

Or query directly:

SELECT tgname, tgrelid::regclass, tgenabled
FROM pg_trigger
WHERE NOT tgisinternal;

Common Use Cases

Troubleshooting Common Issues

Trigger seems to not fire at all — check whether it’s disabled (tgenabled should be 'O' for enabled), and confirm the event type (INSERT/UPDATE/DELETE) actually matches what you’re doing. Also double-check you didn’t attach it to the wrong table.

“stack depth limit exceeded” — usually caused by a trigger that, directly or indirectly, causes another update on the same table, creating infinite recursion. Add guard conditions or use pg_trigger_depth() to detect and prevent recursive calls.

Trigger fires but the change doesn’t stick — for BEFORE triggers, remember you must RETURN NEW (possibly modified) for the change to actually be applied. Returning NULL from a BEFORE trigger cancels the operation entirely.

Performance degradation on bulk inserts — FOR EACH ROW triggers add overhead per row. For very large bulk operations, consider temporarily disabling non-critical triggers, or switching to FOR EACH STATEMENT if per-row detail isn’t actually needed.

Best Practices

Wrapping Up

Triggers let PostgreSQL react automatically to changes in your data, which makes them ideal for auditing, enforcing rules that span multiple rows or tables, and keeping derived data consistent. The trade-off is that they add a layer of behavior that isn’t visible from application code, so use them where they genuinely simplify your system — not just because you can. Once you’re comfortable with the BEFORE/AFTER/INSTEAD OF distinction and how NEW and OLD work, triggers stop feeling like magic and start feeling like just another tool in the toolbox.

Exit mobile version