PostgreSQL Triggers PostgreSQL · Triggers · Intro

PL/pgSQL Triggers — Introduction

Master PostgreSQL trigger fundamentals — the BEFORE/AFTER timing axis, the ROW/STATEMENT scope axis, the five trigger events (INSERT, UPDATE, DELETE, TRUNCATE, INSTEAD OF), and the OLD/NEW/TG_* implicit variables. Includes when triggers are the right tool versus declarative constraints, generated columns, and FK cascades.

What is a Trigger?

A trigger is a database object that fires a function automatically in response to an event on a table — an INSERT, UPDATE, DELETE, or TRUNCATE. The function runs as part of the same transaction as the triggering statement, so its effects either commit together with the change or roll back together with it.

Triggers exist for one main reason: to put logic that must run on data changes inside the database, where every application connecting to that database is forced to obey it. The alternative — relying on every application to do the right thing — only works until someone writes a script that bypasses the rule.

Common uses for triggers	Example
Audit logging	Every UPDATE on `employees` writes the old and new row to `employees_audit`
Validation	A BEFORE INSERT check refuses to add a row with negative salary
Derived data	An UPDATE on order line items recomputes the order total
Materializing views	Changes to base tables propagate to a denormalized reporting table
Cross-table integrity	Inserting a customer also inserts a default loyalty record

Two Dimensions of Trigger Classification

Every trigger is classified along two independent axes. The combination determines when and how often it fires.

Axis 1: Timing — BEFORE vs AFTER

Timing	Fires	Can do	Best for
`BEFORE`	Before the row is written	Modify the row (NEW), cancel the operation (return NULL)	Validation, normalization, computed columns
`AFTER`	After the row is written	Read the final row; trigger other side effects	Audit logging, cascading actions, notifications

Axis 2: Scope — ROW vs STATEMENT

Scope	Fires	Sees	Best for
`FOR EACH ROW`	Once per affected row	OLD and NEW for that row	Per-row validation, audit, derived values
`FOR EACH STATEMENT`	Once per SQL statement, regardless of row count	Transition tables (NEW TABLE / OLD TABLE) in PG ≥10	Bulk-aware logic, statement-level summary actions

💡 Concrete difference. An UPDATE employees SET salary = salary * 1.05; on a 100-row table fires a row-level trigger 100 times (once per row) and a statement-level trigger once.

The Five Trigger Events

Event	Fired by	OLD/NEW available
`INSERT`	INSERT statements	Only NEW
`UPDATE`	UPDATE statements	OLD and NEW
`DELETE`	DELETE statements	Only OLD
`TRUNCATE`	TRUNCATE statements	Neither (statement-level only)
`INSTEAD OF`	DML on a view (special case)	OLD/NEW depending on operation

Inside a row-level trigger function, two implicit record variables let you access the row data:

OLD — the row before the change. Available in UPDATE and DELETE triggers.
NEW — the row after the change. Available in INSERT and UPDATE triggers.

Plus a set of TG_* variables that describe the trigger context:

Variable	Holds
`TG_OP`	The operation: `'INSERT'`, `'UPDATE'`, `'DELETE'`, or `'TRUNCATE'`
`TG_WHEN`	Timing: `'BEFORE'`, `'AFTER'`, or `'INSTEAD OF'`
`TG_LEVEL`	Scope: `'ROW'` or `'STATEMENT'`
`TG_TABLE_NAME`	Name of the table the trigger is on
`TG_TABLE_SCHEMA`	Schema of that table
`TG_NAME`	Name of the trigger itself

What BEFORE Triggers Can Do That AFTER Triggers Can't

The most important practical difference between BEFORE and AFTER:

BEFORE triggers can modify NEW — set defaults, normalize values, fill in derived columns. Whatever you assign to NEW is what gets written to the table.
BEFORE triggers can cancel the operation — return NULL from the trigger function and the row is silently skipped.
AFTER triggers see the final row — including any defaults and serial values that were filled in by the database itself.
AFTER triggers cannot modify NEW — the row's already on disk. Assignment to NEW is silently ignored.

⚠️ Common bug: trying to set a "last_updated" timestamp in an AFTER UPDATE trigger. Doesn't work — the row is already written when AFTER fires. To auto-fill a timestamp column on every update, you must use a BEFORE trigger that sets NEW.last_updated := CURRENT_TIMESTAMP.

PostgreSQL-Specific Trigger Features

PostgreSQL implements the SQL-standard trigger model with several useful extensions:

TRUNCATE triggers — fire on TRUNCATE TABLE. Most other databases skip triggers on TRUNCATE entirely.
INSTEAD OF triggers on views — let you make read-only views writable by defining custom DML handlers.
Triggers must call a separately-defined function — you can't put the trigger body inline in the CREATE TRIGGER statement. The function returns type trigger and is bound to one or more triggers via EXECUTE FUNCTION.
Multiple triggers per event are allowed — fire in alphabetical order by trigger name.
Conditional firing via WHEN clauses — trigger only fires if a condition holds, e.g. WHEN (NEW.salary <> OLD.salary).

When NOT to Use a Trigger

Triggers are powerful, but invisible — they fire automatically and don't show up in the SQL you're reading. That makes them easy to forget about, hard to debug, and dangerous when overused. Some signs that a trigger isn't the right answer:

Situation	Better tool
Computing a value from other columns	`GENERATED ALWAYS AS (...) STORED` column — visible in the table definition
Constraint enforcement	`CHECK`, `UNIQUE`, `FOREIGN KEY`, or exclusion constraints — declarative and indexable
Cross-table cascades on delete	`ON DELETE CASCADE` on the foreign key — same effect, no trigger
Rate-limited side effects (sending emails, calling APIs)	Application code or a job queue — triggers run synchronously inside the transaction and slow it down
Logic that varies by application or context	Application code — a trigger applies uniformly to every connection

Use triggers when the rule must be enforced at the database level and declarative constraints can't express it. Audit logging is the canonical case.

Best Practices Overview

Keep triggers fast. They run in the transaction's hot path; slow triggers slow every write.
Document them visibly. A trigger fires invisibly — at minimum, comment the table to mention which triggers fire on it.
Don't catch errors silently. A WHEN OTHERS THEN return NEW; in a trigger swallows real bugs.
Don't write to the same table the trigger is on from inside a row-level trigger — you'll trigger the trigger again. Statement-level can sometimes work; usually a sign to refactor.
Use BEFORE for changing data and AFTER for reacting to data. The mental model holds in 95% of cases.
Prefer declarative constraints when they suffice. A CHECK constraint is faster, simpler, and visible in the schema; a trigger has to be opened to read.

Conclusion

A trigger is a database-level handler that runs a function automatically when an INSERT, UPDATE, DELETE, or TRUNCATE happens.
Two independent axes classify triggers: timing (BEFORE / AFTER / INSTEAD OF) and scope (ROW / STATEMENT).
Inside a row-level trigger, OLD and NEW hold the before- and after-images. TG_* variables describe the trigger context.
BEFORE triggers can change data (modify NEW, return NULL to skip). AFTER triggers can only react to data already written.
PostgreSQL adds TRUNCATE triggers and INSTEAD OF triggers on views to the standard model.
Use triggers when database-level enforcement matters and declarative constraints don't suffice. Audit logging is the canonical use case.