Triggers

Introduction

A trigger runs code automatically when rows are inserted, updated, or deleted—BEFORE, AFTER, or INSTEAD OF the event. In PostgreSQL, triggers call a trigger function written in PL/pgSQL. Use them for audit logs, derived columns, or cross-row rules—and know the pitfalls: hidden logic, recursion, and extra write load. Compare with MySQL triggers.

Prerequisites

• Functions and PL/pgSQL
• Constraints and Keys
• Tables users and posts in your practice database

PostgreSQL Trigger Model

Unlike MySQL, the trigger body is a function; the trigger object wires events to that function:

CREATE FUNCTION trigger_fn() ... RETURN TRIGGER;
CREATE TRIGGER trigger_name ... EXECUTE FUNCTION trigger_fn();

Code explanation:

• Trigger function must RETURN TRIGGER (or RETURN NULL to skip row in some BEFORE cases)
• One function can serve multiple triggers; one trigger calls one function

Audit Log Example

Log table:

CREATE TABLE post_audit (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id BIGINT NOT NULL,
  action TEXT NOT NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Trigger function:

CREATE OR REPLACE FUNCTION tr_post_audit_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO post_audit (post_id, action)
  VALUES (NEW.id, TG_OP);
  RETURN NEW;
END;
$$;

TG_OP is INSERT, UPDATE, or DELETE.

Attach trigger:

CREATE TRIGGER tr_posts_after_update
AFTER UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION tr_post_audit_fn();

Test:

UPDATE posts SET title = 'Updated title' WHERE id = 1;
 
SELECT * FROM post_audit;

Log all DML on posts:

CREATE TRIGGER tr_posts_after_insert
AFTER INSERT ON posts
FOR EACH ROW
EXECUTE FUNCTION tr_post_audit_fn();
 
CREATE TRIGGER tr_posts_after_delete
AFTER DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION tr_post_audit_fn();

For DELETE, use OLD.id in the function when TG_OP = 'DELETE':

BEFORE INSERT: Validate or Default

Reject empty titles before the row is stored:

Code explanation:

• RAISE EXCEPTION aborts the statement—similar to MySQL SIGNAL
• BEFORE triggers can modify NEW columns before write
• Prefer CHECK constraints when the rule is simple—Constraints and Keys

NEW and OLD

Detect first publish:

-- Inside trigger function:
-- IF OLD.published_at IS NULL AND NEW.published_at IS NOT NULL THEN ...

WHEN Clause (Filter Events)

Fire only when specific columns change:

CREATE TRIGGER tr_posts_title_change
AFTER UPDATE OF title ON posts
FOR EACH ROW
WHEN (OLD.title IS DISTINCT FROM NEW.title)
EXECUTE FUNCTION tr_post_audit_fn();

Code explanation:

• UPDATE OF title limits column scope
• IS DISTINCT FROM treats NULL safely—better than <> for nullable columns

INSTEAD OF Triggers (Views)

Make a complex view updatable by redirecting writes:

See Views and Materialized Views.

Inspect and Drop Triggers

-- psql
\d posts
 
SELECT tgname, pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgrelid = 'posts'::regclass AND NOT tgisinternal;

Drop:

DROP TRIGGER IF EXISTS tr_posts_after_update ON posts;
DROP FUNCTION IF EXISTS tr_post_audit_fn();

Drop triggers before dropping their functions if the function is trigger-only.

Triggers vs Constraints

Use Cases and Warnings

FAQ

FOR EACH ROW vs FOR EACH STATEMENT?

FOR EACH ROW fires per row (most common). FOR EACH STATEMENT fires once per SQL command—advanced bulk cases.

Can triggers see uncommitted data?

They run inside the same transaction as the triggering statement—audit rows roll back if the transaction rolls back.

EXECUTE FUNCTION vs PROCEDURE?

Triggers call functions returning TRIGGER, not procedures.

MySQL DELIMITER equivalent?

Use $$ ... $$ function bodies—Functions and PL/pgSQL.

Trigger slowed bulk INSERT?

Each row invokes PL/pgSQL—disable triggers temporarily only with care (ALTER TABLE ... DISABLE TRIGGER—superuser).

Replace MySQL one-body trigger?

Split into function + CREATE TRIGGER—two objects to migrate and version.