PG
PostgreSQLSQLiteMariaDB
⚡

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

TG_OPNEWOLDTG_TABLE_NAMETG_WHENtsvector_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.

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
⚠Anti-Pattern

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
See also:new_recordold_recordtg_table_nametg_when

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
⚠Anti-Pattern

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
See also:old_recordtg_oprow_to_json

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
⚠Anti-Pattern

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
See also:new_recordtg_oprow_to_json

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
⚠Anti-Pattern

`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
See also:tg_optg_whentg_table_schema

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"
⚠Anti-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
See also:tg_optg_table_namenew_record

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

ParameterTypeDescription
tsvector_colidentifierThe tsvector column to update
configtextText search configuration (e.g., 'pg_catalog.english')
text_colsidentifiersText 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
⚠Anti-Pattern

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
See also:to_tsvectorto_tsquerynew_record

Related PostgreSQL Categories

System Administration →System Information →