⚡

PostgreSQL Trigger Functions & Special Variables

Complete reference for PostgreSQL trigger special variables including TG_OP, TG_TABLE_NAME, TG_WHEN, TG_LEVEL, NEW, and OLD. Covers row-level vs statement-level triggers, BEFORE vs AFTER, transition tables with NEW TABLE and OLD TABLE, and event trigger functions. Updated for PostgreSQL 16.

6 functions

Jump to function

NEW OLD TG_OP TG_TABLE_NAME TG_WHEN tsvector_update_trigger

What are PostgreSQL Trigger Functions & Special Variables?

PostgreSQL trigger functions are PL/pgSQL functions executed automatically before or after INSERT, UPDATE, DELETE, or TRUNCATE statements. Within a trigger function, special variables provide context: NEW is the new row being inserted or updated, OLD is the row being updated or deleted, TG_OP is the operation type, and TG_TABLE_NAME is the table being modified. Understanding these variables is essential for writing audit logs, enforcing complex constraints, and maintaining denormalised data.

NEW

PG 7.4+→ record

In a row-level trigger, contains the new row for INSERT and UPDATE operations. NULL for DELETE triggers.

DeveloperDBA

Signature

NEW → record

Examples

sql

NEW.updated_at := now();

→Sets updated_at on the new row before INSERT/UPDATE

sql

IF NEW.email IS DISTINCT FROM OLD.email THEN
  NEW.email_verified := false;
END IF;

→Resets verification when email changes

sql

-- Auto-set updated_at on every UPDATE
IF TG_OP = 'UPDATE' THEN
  NEW.updated_at := now();
END IF;
RETURN NEW;

→updated_at always reflects the time of the last modification

sql

-- Auto-fill search_vector from title + body before INSERT or UPDATE
NEW.search_vector := to_tsvector('english',
  coalesce(NEW.title, '') || ' ' || coalesce(NEW.body, ''));
RETURN NEW;

→Full-text search vector auto-maintained without a separate job

⚠Anti-Pattern— Forgetting RETURN NEW in a BEFORE ROW trigger silently cancels the operation

A PL/pgSQL function returns NULL by default if no RETURN statement is reached. In a BEFORE ROW trigger, returning NULL suppresses the INSERT or UPDATE without any error or warning — the operation simply disappears.

✓ Instead: Always end BEFORE ROW trigger functions with `RETURN NEW;` for INSERT/UPDATE, or `RETURN OLD;` for DELETE. Add a guard: `RETURN NEW;` unconditionally after your logic.

In a BEFORE trigger, you can modify `NEW` fields before the row is written to the table. Return `NEW` to proceed with the (possibly modified) row, or return `NULL` to suppress the operation entirely.

example

NEW.search_vector := to_tsvector('english', coalesce(NEW.title,'') || ' ' || coalesce(NEW.body,''));
RETURN NEW;

→Auto-updates search_vector before INSERT/UPDATE

OLD

PG 7.4+→ record

In a row-level trigger, contains the old row for UPDATE and DELETE operations. NULL for INSERT triggers.

DeveloperDBA

Signature

OLD → record

Examples

sql

INSERT INTO change_log (table_name, pk, old_data) VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD));

→Logs the old row data on DELETE

sql

IF NEW.status = 'complete' AND OLD.status != 'complete' THEN
  NEW.completed_at := now();
END IF;

→Sets completed_at when status transitions to complete

sql

-- Only log when the status actually changes
IF OLD.status IS DISTINCT FROM NEW.status THEN
  INSERT INTO status_history (entity_id, from_status, to_status, changed_at)
  VALUES (OLD.id, OLD.status, NEW.status, now());
END IF;
RETURN NEW;

→Status change history recorded only for genuine transitions

sql

-- Prevent re-opening a closed order
IF OLD.status = 'closed' AND NEW.status <> 'closed' THEN
  RAISE EXCEPTION 'closed orders cannot be re-opened';
END IF;
RETURN NEW;

→Update blocked when trying to change status away from 'closed'

⚠Anti-Pattern— Accessing OLD in an INSERT trigger causes a runtime error (OLD is NULL for INSERT)

OLD is NULL for INSERT operations. Referencing `OLD.id` in a trigger that fires on INSERT raises a runtime error when the trigger executes — even if the trigger function was created successfully.

✓ Instead: Always check `TG_OP` before accessing OLD: `IF TG_OP IN ('UPDATE', 'DELETE') THEN -- use OLD END IF;`. Or install separate trigger functions for INSERT versus UPDATE/DELETE.

Compare `OLD.col IS DISTINCT FROM NEW.col` to detect real changes (null-safe). Store `row_to_json(OLD)` in an audit table to have the full pre-change state without tracking each column individually.

example

INSERT INTO audit_trail (ts, op, pk, old_row, new_row) VALUES (now(), TG_OP, OLD.id, to_jsonb(OLD), to_jsonb(NEW));

→Full before/after row state in audit log

Common Gotchas

⚠

Foreign keys don't auto-create an index on the referencing column

PostgreSQL creates an index on the primary key side of a FK, but NOT on the foreign key column. Missing FK indexes cause slow lookups and lock escalation on DELETE.

TG_OP

PG 7.4+→ text

In a trigger function, contains the operation that fired the trigger: 'INSERT', 'UPDATE', 'DELETE', or 'TRUNCATE'.

DeveloperDBA

Signature

TG_OP → text

Examples

sql

IF TG_OP = 'INSERT' THEN
  INSERT INTO audit_log (action, new_data) VALUES ('INSERT', row_to_json(NEW));
ELSIF TG_OP = 'UPDATE' THEN
  INSERT INTO audit_log (action, old_data, new_data) VALUES ('UPDATE', row_to_json(OLD), row_to_json(NEW));
END IF;

→Audit log with operation type

sql

IF TG_OP = 'DELETE' THEN
  INSERT INTO audit_log (action, old_data) VALUES ('DELETE', row_to_json(OLD));
  RETURN OLD;
END IF;

→DELETE operations logged with OLD row data; function returns OLD to proceed

sql

IF TG_OP = 'TRUNCATE' THEN
  INSERT INTO audit_log (action, table_name) VALUES ('TRUNCATE', TG_TABLE_NAME);
  RETURN NULL;
END IF;

→TRUNCATE event logged with the table name

sql

CASE TG_OP
  WHEN 'INSERT' THEN UPDATE stats SET insert_count = insert_count + 1 WHERE tbl = TG_TABLE_NAME;
  WHEN 'UPDATE' THEN UPDATE stats SET update_count = update_count + 1 WHERE tbl = TG_TABLE_NAME;
  WHEN 'DELETE' THEN UPDATE stats SET delete_count = delete_count + 1 WHERE tbl = TG_TABLE_NAME;
END CASE;
RETURN NEW;

→Per-operation row counters incremented in a statistics table

⚠Anti-Pattern— CASE on TG_OP without ELSE causes silent NULL return in TRUNCATE triggers

A CASE or IF-ELSIF chain on TG_OP that lacks an ELSE branch will fall through to NULL for TRUNCATE triggers. Returning NULL from a BEFORE trigger suppresses the operation without any error message.

✓ Instead: Always include an ELSE branch: `ELSE RETURN NULL;` for unsupported operations, or `ELSE RAISE EXCEPTION 'unexpected TG_OP: %', TG_OP;` if the trigger should never fire for other operations.

One PL/pgSQL trigger function can handle all three DML operations by switching on `TG_OP`. This reduces code duplication versus separate functions per operation.

example

CREATE TRIGGER audit_all AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION audit_trigger();

→Single trigger function handling all operations

TG_TABLE_NAME

PG 7.4+→ name

In a trigger function, contains the name of the table that caused the trigger to fire.

DeveloperDBA

Signature

TG_TABLE_NAME → name

Examples

sql

INSERT INTO audit_log (table_name, operation) VALUES (TG_TABLE_NAME, TG_OP);

→Logs which table was modified

sql

-- Use TG_TABLE_NAME and TG_TABLE_SCHEMA together for unambiguous identification
INSERT INTO audit_log (schema_name, table_name, operation, actor)
VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, session_user);

→Audit row with fully qualified table identity and authenticated user

sql

-- Route to per-table archive using safe dynamic SQL
EXECUTE format(
  'INSERT INTO %I SELECT $1.*',
  TG_TABLE_NAME || '_archive'
) USING OLD;

→OLD row moved to a matching _archive table using safe identifier quoting

sql

-- Count rows affected per table in a shared trigger
INSERT INTO change_counters (tbl, cnt)
VALUES (TG_TABLE_NAME, 1)
ON CONFLICT (tbl) DO UPDATE SET cnt = change_counters.cnt + 1;

→Per-table change counter upserted atomically from a shared trigger function

⚠Anti-Pattern— Using TG_TABLE_NAME in dynamic SQL without format('%I') causes SQL injection

Constructing `EXECUTE 'SELECT * FROM ' || TG_TABLE_NAME` without quoting allows a table named with SQL metacharacters to execute arbitrary SQL. This is a critical security vulnerability in trigger functions.

✓ Instead: Always use `format('%I', TG_TABLE_NAME)` when building dynamic SQL: `EXECUTE format('SELECT * FROM %I', TG_TABLE_NAME)`. The `%I` formatter applies proper identifier quoting.

`TG_TABLE_NAME` lets one trigger function serve multiple tables. Install the same trigger on users, orders, products etc. and the log will show which table each change came from.

example

CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION generic_audit();
CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION generic_audit();

→One audit function logs changes from multiple tables

TG_WHEN

PG 7.4+→ text

In a trigger function, contains 'BEFORE', 'AFTER', or 'INSTEAD OF' indicating when the trigger fired.

DeveloperDBA

Signature

TG_WHEN → text

Examples

sql

RAISE NOTICE 'Trigger fired % on %', TG_WHEN, TG_TABLE_NAME;

→"Trigger fired BEFORE on users"

sql

-- Guard against INSTEAD OF being applied to a real table
IF TG_WHEN = 'INSTEAD OF' AND TG_RELKIND <> 'v' THEN
  RAISE EXCEPTION 'INSTEAD OF trigger must only be used on views';
END IF;

→Exception raised if trigger is accidentally attached to a base table

sql

-- Log timing context alongside the audit record
INSERT INTO trigger_audit (event_time, tbl, when_fired, op)
VALUES (now(), TG_TABLE_NAME, TG_WHEN, TG_OP);

→Audit row records whether the trigger fired BEFORE or AFTER the statement

sql

-- Enforce that data normalisation only happens in BEFORE triggers
IF TG_WHEN <> 'BEFORE' THEN
  RAISE EXCEPTION 'normalise_trigger must be BEFORE, not %', TG_WHEN;
END IF;
NEW.email := lower(trim(NEW.email));
RETURN NEW;

→Email normalised to lowercase + trimmed only when trigger timing is correct

⚠Anti-Pattern— Placing irreversible side effects (emails, external API calls) in BEFORE triggers

Side effects executed in a BEFORE trigger run before the row is committed. If the transaction rolls back after the side effect (due to a later constraint violation), the external action cannot be undone — leading to emails sent for cancelled orders or API calls for rolled-back data.

✓ Instead: Place irreversible side effects in AFTER triggers (the row is committed before the trigger fires) or, better, in application code after a successful COMMIT. For robust delivery, use a transactional outbox pattern.

Use BEFORE triggers when you need to modify the data being written (e.g., auto-filling timestamps). Use AFTER triggers for side effects like updating related tables or sending notifications — the row is already committed.

example

-- BEFORE: modify the row
CREATE TRIGGER set_ts BEFORE INSERT OR UPDATE ON events FOR EACH ROW EXECUTE FUNCTION set_timestamps();
-- AFTER: side effect
CREATE TRIGGER notify AFTER INSERT ON events FOR EACH ROW EXECUTE FUNCTION notify_listeners();

→Correct trigger timing for each use case

tsvector_update_trigger

PG 8.3+→ trigger

A built-in trigger function that automatically updates a tsvector column from one or more text columns. Used to maintain full-text search vectors.

DeveloperDBA

Signature

tsvector_update_trigger ( ) → trigger

Parameters

Parameter	Type	Description
tsvector_col	identifier	The tsvector column to update
config	text	Text search configuration (e.g., 'pg_catalog.english')
text_cols	identifiers	Text columns to index

Examples

sql

ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX ON articles USING GIN(search_vector);
CREATE TRIGGER upd_tsvector BEFORE INSERT OR UPDATE ON articles FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

→Auto-maintained full-text search index

sql

-- Backfill existing rows after adding the trigger
UPDATE articles SET title = title;
-- Forces trigger to fire on all rows, populating search_vector

→All rows re-indexed; search_vector is now non-NULL for every existing row

sql

-- Add the trigger for multi-column full-text indexing
CREATE TRIGGER tsvec_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(
  search_vector, 'pg_catalog.english', title, body, tags
);

→search_vector auto-updated from title, body, and tags on every write

sql

-- Query using the trigger-maintained tsvector
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & index') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

→Top 10 articles matching the full-text query, ranked by relevance

⚠Anti-Pattern— tsvector_update_trigger with NULL text columns produces NULL tsvector, hiding rows

When a text column passed to `tsvector_update_trigger` contains NULL, the resulting tsvector is NULL. Rows with NULL tsvectors are invisible to full-text search queries, silently disappearing from search results.

✓ Instead: Use a custom trigger or a GENERATED column with `COALESCE`: `to_tsvector('english', COALESCE(title,'') || ' ' || COALESCE(body,''))` to handle NULL columns gracefully.

Since PostgreSQL 12, use `GENERATED ALWAYS AS (to_tsvector('english', col1 || ' ' || col2)) STORED` instead of a trigger. Generated columns are simpler, don't require a trigger, and support backfilling automatically.

example

ALTER TABLE articles ADD COLUMN tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;
CREATE INDEX ON articles USING GIN(tsv);

→Auto-maintained FTS vector without a trigger

PostgreSQL Trigger Functions & Special Variables

Jump to function

What are PostgreSQL Trigger Functions & Special Variables?

NEW

Signature

Examples

OLD

Signature

Examples

Common Gotchas

TG_OP

Signature

Examples

TG_TABLE_NAME

Signature

Examples

TG_WHEN

Signature

Examples

tsvector_update_trigger

Signature

Parameters

Examples

Related PostgreSQL Categories