PostgreSQL Triggers: BEFORE/AFTER INSERT UPDATE DELETE with Examples

PostgreSQL Database Triggers: Complete Guide

Triggers automatically execute when data changes. They’re powerful for maintaining data integrity, logging changes, and enforcing complex business rules that can’t be expressed in CHECK constraints.

This guide covers trigger timing (BEFORE/AFTER), events (INSERT/UPDATE/DELETE), and real-world patterns you’ll encounter.

What You’ll Learn

This guide covers:

Trigger timing: BEFORE vs AFTER
Trigger events: INSERT, UPDATE, DELETE
NEW and OLD values
Trigger functions and TG_OP
FOR EACH ROW vs FOR EACH STATEMENT
Conditional triggers (WHEN clause)
Audit logging patterns
Cascading updates and deletions
Performance considerations

Trigger Basics

Triggers have three dimensions:

When: BEFORE (before change) or AFTER (after change)
Event: INSERT, UPDATE, or DELETE
Scope: FOR EACH ROW (once per affected row) or FOR EACH STATEMENT (once total)

Most triggers are BEFORE INSERT OR UPDATE FOR EACH ROW.

Creating Your First Trigger

Validation Trigger (BEFORE INSERT)

Validate data before it enters the database.

-- Create trigger function
CREATE FUNCTION validate_employee_data() RETURNS TRIGGER AS $$
BEGIN
    -- Validate salary is positive
    IF NEW.salary <= 0 THEN
        RAISE EXCEPTION 'Salary must be positive';
    END IF;

    -- Validate hire date is not in future
    IF NEW.hire_date > CURRENT_DATE THEN
        RAISE EXCEPTION 'Hire date cannot be in the future';
    END IF;

    -- Validate email is not empty
    IF NEW.email IS NULL OR NEW.email = '' THEN
        RAISE EXCEPTION 'Email is required';
    END IF;

    RETURN NEW;  -- Allow the insert
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER trigger_validate_employee
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_employee_data();

-- Test the trigger
INSERT INTO employees (emp_id, first_name, last_name, email, salary, dept_id, hire_date)
VALUES (100, 'John', 'Doe', '[email protected]', 80000, 1, '2026-02-21');
-- Success

-- This will fail
INSERT INTO employees (emp_id, first_name, last_name, email, salary, dept_id, hire_date)
VALUES (101, 'Jane', 'Smith', '[email protected]', -5000, 1, '2026-02-21');
-- ERROR: Salary must be positive

The function receives NEW (the incoming row). Returning NEW allows the operation. Returning NULL would abort it.

Audit Logging with AFTER Triggers

Track All Changes

Log every INSERT, UPDATE, DELETE to an audit table.

CREATE TABLE employee_audit (
    audit_id SERIAL PRIMARY KEY,
    operation VARCHAR(10),  -- INSERT, UPDATE, DELETE
    emp_id INTEGER,
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by VARCHAR(50) DEFAULT SESSION_USER,
    old_values JSONB,
    new_values JSONB
);

-- Trigger function
CREATE FUNCTION audit_employee_changes() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO employee_audit (operation, emp_id, new_values)
        VALUES ('INSERT', NEW.emp_id, row_to_json(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_audit (operation, emp_id, old_values, new_values)
        VALUES ('UPDATE', NEW.emp_id, row_to_json(OLD), row_to_json(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employee_audit (operation, emp_id, old_values)
        VALUES ('DELETE', OLD.emp_id, row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for all operations
CREATE TRIGGER trigger_audit_employee
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_employee_changes();

-- Test: Insert employee
INSERT INTO employees (emp_id, first_name, last_name, email, salary, dept_id, hire_date)
VALUES (102, 'Alice', 'Johnson', '[email protected]', 90000, 2, '2026-02-21');

-- Check audit log
SELECT operation, emp_id, changed_at, new_values
FROM employee_audit
WHERE emp_id = 102;

Result:

operation | emp_id | changed_at          | new_values
-----------+--------+---------------------+-------------------------------------
INSERT    |    102 | 2026-02-21 10:30:00 | {"emp_id": 102, "first_name": "Alice", ...}

TG_OP contains the operation type (INSERT, UPDATE, DELETE). row_to_json() converts the entire row to JSON.

BEFORE vs AFTER Triggers

BEFORE Triggers: Modify Data

Use BEFORE to modify incoming data before it’s saved.

CREATE FUNCTION normalize_employee_data() RETURNS TRIGGER AS $$
BEGIN
    -- Trim whitespace
    NEW.first_name := TRIM(NEW.first_name);
    NEW.last_name := TRIM(NEW.last_name);
    NEW.email := LOWER(TRIM(NEW.email));

    -- Set default values
    IF NEW.created_at IS NULL THEN
        NEW.created_at := NOW();
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_normalize_employee
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION normalize_employee_data();

-- Insert with extra spaces and uppercase email
INSERT INTO employees (first_name, last_name, email, salary, dept_id, hire_date)
VALUES ('  John  ', '  Smith  ', '  [email protected]  ', 85000, 1, '2026-02-21');

-- Stored as 'John', 'Smith', '[email protected]' (trimmed and lowercased)

AFTER Triggers: React to Changes

Use AFTER to trigger actions based on the change.

CREATE TABLE salary_changes (
    change_id SERIAL PRIMARY KEY,
    emp_id INTEGER,
    old_salary DECIMAL,
    new_salary DECIMAL,
    change_date TIMESTAMP DEFAULT NOW(),
    change_percent DECIMAL
);

CREATE FUNCTION log_salary_changes() RETURNS TRIGGER AS $$
DECLARE
    v_change_pct DECIMAL;
BEGIN
    IF TG_OP = 'UPDATE' AND NEW.salary != OLD.salary THEN
        v_change_pct := ((NEW.salary - OLD.salary) / OLD.salary) * 100;

        INSERT INTO salary_changes (emp_id, old_salary, new_salary, change_percent)
        VALUES (NEW.emp_id, OLD.salary, NEW.salary, v_change_pct);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_salary_changes
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_changes();

-- Update salary
UPDATE employees SET salary = 95000 WHERE emp_id = 1;

-- Check changes log
SELECT * FROM salary_changes WHERE emp_id = 1;

Result:

change_id | emp_id | old_salary | new_salary | change_date         | change_percent
-----------+--------+------------+----------+---------------------+--------------
         1 |      1 | 80000      | 95000    | 2026-02-21 10:35:00 | 18.75

NEW and OLD Values

INSERT, UPDATE, DELETE Trigger Values

CREATE FUNCTION explain_new_old() RETURNS TRIGGER AS $$
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            -- Only NEW exists (no previous row)
            RAISE NOTICE 'Inserted: ID %, Name: %', NEW.emp_id, NEW.first_name;
            RETURN NEW;

        WHEN 'UPDATE' THEN
            -- Both OLD and NEW exist
            RAISE NOTICE 'Updated: ID % from % to %', NEW.emp_id, OLD.salary, NEW.salary;
            RETURN NEW;

        WHEN 'DELETE' THEN
            -- Only OLD exists (row being deleted)
            RAISE NOTICE 'Deleted: ID %, Name: %', OLD.emp_id, OLD.first_name;
            RETURN OLD;  -- Must return OLD for DELETE
    END CASE;
END;
$$ LANGUAGE plpgsql;

Key points:

INSERT: Only NEW exists. Return NEW.
UPDATE: Both OLD and NEW exist. Return NEW.
DELETE: Only OLD exists. Return OLD.

Conditional Triggers (WHEN Clause)

Execute trigger only for specific conditions.

-- Only audit salary changes > 10%
CREATE TRIGGER trigger_audit_major_salary_changes
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (
    (NEW.salary - OLD.salary) / OLD.salary > 0.10
    OR (NEW.salary - OLD.salary) / OLD.salary < -0.10
)
EXECUTE FUNCTION log_salary_changes();

-- Only validate on INSERT (not UPDATE)
CREATE TRIGGER trigger_validate_on_insert
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_employee_data();

-- Only if specific column changed
CREATE TRIGGER trigger_on_dept_change
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.dept_id != NEW.dept_id)
EXECUTE FUNCTION handle_department_change();

The WHEN clause prevents unnecessary function calls.

Real-World Trigger Patterns

Automatically Update Modified Timestamp

CREATE TABLE documents (
    doc_id INTEGER PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    modified_at TIMESTAMP DEFAULT NOW()
);

CREATE FUNCTION update_modified_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_modified
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_modified_timestamp();

-- Update a document
UPDATE documents SET title = 'New Title' WHERE doc_id = 1;
-- modified_at is automatically set to NOW()

Enforce Referential Integrity

CREATE FUNCTION prevent_orphan_deletion() RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM projects
        WHERE dept_id = OLD.dept_id
    ) THEN
        RAISE EXCEPTION 'Cannot delete department with active projects';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_prevent_dept_deletion
BEFORE DELETE ON departments
FOR EACH ROW
EXECUTE FUNCTION prevent_orphan_deletion();

Update Aggregated Data

Maintain summary tables when detail tables change.

CREATE TABLE department_summary (
    dept_id INTEGER PRIMARY KEY,
    total_employees INTEGER,
    avg_salary DECIMAL,
    total_payroll DECIMAL
);

CREATE FUNCTION update_dept_summary() RETURNS TRIGGER AS $$
BEGIN
    UPDATE department_summary
    SET
        total_employees = (SELECT COUNT(*) FROM employees WHERE dept_id = NEW.dept_id),
        avg_salary = (SELECT AVG(salary) FROM employees WHERE dept_id = NEW.dept_id),
        total_payroll = (SELECT SUM(salary) FROM employees WHERE dept_id = NEW.dept_id)
    WHERE dept_id = NEW.dept_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Managing Triggers

List Triggers

-- Find all triggers
SELECT trigger_name, event_object_table, event_manipulation
FROM information_schema.triggers
WHERE trigger_schema = 'public'
ORDER BY trigger_name;

Result:

trigger_name                    | event_object_table | event_manipulation
---------------------------------+--------------------+---------------------
trigger_validate_employee       | employees          | INSERT
trigger_audit_employee_changes  | employees          | INSERT, UPDATE, DELETE
trigger_update_modified         | documents          | UPDATE

Disable/Enable Triggers

-- Disable all triggers on a table
ALTER TABLE employees DISABLE TRIGGER ALL;

-- Re-enable
ALTER TABLE employees ENABLE TRIGGER ALL;

-- Disable specific trigger
ALTER TABLE employees DISABLE TRIGGER trigger_validate_employee;

Drop Triggers

DROP TRIGGER trigger_validate_employee ON employees;
DROP TRIGGER trigger_audit_employee_changes ON employees;

Common Errors & Solutions

Error: “Trigger function must return trigger”

Problem:

CREATE FUNCTION my_trigger() RETURNS VARCHAR AS $$
BEGIN
    RETURN 'Done';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_trig AFTER INSERT ON table FOR EACH ROW EXECUTE FUNCTION my_trigger();

Trigger functions must return TRIGGER type.

Solution:

CREATE FUNCTION my_trigger() RETURNS TRIGGER AS $$
BEGIN
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Error: “NEW/OLD not allowed in context”

Problem:

CREATE TRIGGER my_trig
AFTER DELETE ON table
FOR EACH ROW
EXECUTE FUNCTION my_func();

-- Inside function:
-- Use NEW (doesn't exist in DELETE)
INSERT INTO audit (value) VALUES (NEW.id);

Can’t access NEW in DELETE trigger.

Solution:

Check trigger event:

CREATE FUNCTION my_func() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit (value) VALUES (OLD.id);
    ELSE
        INSERT INTO audit (value) VALUES (NEW.id);
    END IF;
    RETURN COALESCE(NEW, OLD);  -- Return appropriate value
END;
$$ LANGUAGE plpgsql;

Infinite Recursion

Problem:

-- Trigger updates same table it's on
CREATE TRIGGER my_trig AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_employee_summary();

-- Inside function:
UPDATE employees SET calculated_field = something WHERE id = NEW.id;
-- This fires the trigger again! Infinite loop.

Solution:

Use BEFORE triggers that prevent cascading updates, or use constraints:

CREATE FUNCTION no_recursive_update() RETURNS TRIGGER AS $$
BEGIN
    -- Check if already updated this row in this transaction
    IF NEW.updated_flag THEN
        RETURN NEW;
    END IF;

    NEW.updated_flag := TRUE;
    -- Safe to update now
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Performance Tips

1. Keep Triggers Fast

-- Slow: Complex logic in trigger
CREATE TRIGGER slow_trigger
AFTER INSERT ON table
FOR EACH ROW
EXECUTE FUNCTION complex_calculation();

-- Better: Defer heavy logic
CREATE TRIGGER fast_trigger
AFTER INSERT ON table
FOR EACH ROW
EXECUTE FUNCTION mark_for_processing();

-- Process later in batch job

2. Use BEFORE for Bulk Inserts

-- BEFORE trigger: applied once per row during bulk insert
-- AFTER trigger: must be applied after insert completes
-- BEFORE is faster for large batches
CREATE TRIGGER before_insert_performance
BEFORE INSERT ON table FOR EACH ROW ...

3. Index Lookup Columns

-- If trigger does lookups
CREATE INDEX idx_dept_id ON departments(dept_id);

-- Trigger can use the index
SELECT name FROM departments WHERE dept_id = NEW.dept_id;

4. Avoid STATEMENT-level Triggers

-- Slower: Fires once per statement
CREATE TRIGGER statement_trigger
AFTER INSERT ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION log_changes();

-- Better: Fire per row
CREATE TRIGGER row_trigger
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_change();

FAQ

Q: Should triggers modify the table they’re defined on? A: No, avoid if possible (causes recursion). Use BEFORE triggers that return modified NEW instead.

Q: What’s the performance impact of triggers? A: BEFORE validation triggers have minimal impact. AFTER audit triggers add ~5-10% overhead. Measure with your data.

Q: Can triggers call other functions? A: Yes, freely. Triggers are just functions.

Q: Can I have multiple triggers on the same event? A: Yes, they execute in creation order. Name triggers clearly.

Q: How do I log trigger execution? A: Use RAISE NOTICE in development. Use audit tables in production.

Q: Should I use triggers or constraints? A: Constraints for simple checks. Triggers for complex logic or multi-table rules.

Continue mastering database logic:

PostgreSQL Functions, Procedures & Triggers - Overview of all database programming
Writing PL/pgSQL Functions - Master PL/pgSQL syntax
Error Handling with Exceptions - Robust error handling in triggers

Triggers are powerful for data integrity and automation. Use them wisely to enforce business rules at the database level.

PostgreSQL Database Triggers: Complete Guide to BEFORE, AFTER & Events

PostgreSQL Database Triggers: Complete Guide

What You’ll Learn

Trigger Basics

Creating Your First Trigger

Validation Trigger (BEFORE INSERT)

Audit Logging with AFTER Triggers

Track All Changes

BEFORE vs AFTER Triggers

BEFORE Triggers: Modify Data

AFTER Triggers: React to Changes

NEW and OLD Values

INSERT, UPDATE, DELETE Trigger Values

Conditional Triggers (WHEN Clause)

Real-World Trigger Patterns

Automatically Update Modified Timestamp

Enforce Referential Integrity

Update Aggregated Data

Managing Triggers

List Triggers

Disable/Enable Triggers

Drop Triggers

Common Errors & Solutions

Error: “Trigger function must return trigger”

Error: “NEW/OLD not allowed in context”

Infinite Recursion

Performance Tips

FAQ

Related Topics