SQL Triggers

Imagine you want the database to automatically log every change to your accounts table, validate data before it is inserted, or update a running total whenever an order is placed. You could add this logic to every application that touches the database, but then you need to remember it in every script, every API endpoint, every admin tool, and every migration. Miss one and your audit trail has gaps, your validation has holes, or your totals are wrong.

SQL triggers solve this by embedding automatic logic directly into the database. A trigger is a block of code that fires automatically when a specific event (insert, update, or delete) occurs on a table. The logic executes inside the database engine itself, regardless of which application, user, or script made the change. Nothing slips through.

This guide covers what triggers are, how BEFORE and AFTER triggers work, how to write triggers for INSERT, UPDATE, and DELETE events, practical use cases like audit logging and validation, and an important caution about trigger overuse. Every concept includes working PostgreSQL examples with clear before-and-after states.

What Are Triggers?

A trigger is a named database object that automatically executes a specified block of code (the trigger function) in response to a data modification event on a table. You define when it fires, what event activates it, and what it does.

Trigger Anatomy:

Aspect	Details
WHEN does it fire?	BEFORE or AFTER the event
WHAT event triggers?	INSERT, UPDATE, or DELETE
ON which table?	The target table
HOW often?	Once per ROW or STATEMENT
WHAT does it do?	The trigger function

Why Use Triggers?

Use Case	Why a Trigger Is Ideal
Audit logging	Automatically record who changed what and when, regardless of the source of the change
Data validation	Enforce complex rules that CHECK constraints cannot express
Derived values	Auto-calculate fields like updated_at, total, or full_name
Referential actions	Cascade custom logic when related data changes
Synchronization	Keep denormalized or summary tables in sync with source data

The Sample Data

We will use these tables throughout the guide:

employees table:

id	name	department	salary	updated_at
1	Alice	Engineering	95000	2024-01-15 09:00:00
2	Bob	Engineering	88000	2024-01-15 09:00:00
3	Charlie	Sales	72000	2024-01-15 09:00:00

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

INSERT INTO employees (id, name, department, salary, updated_at) VALUES
(1, 'Alice',   'Engineering', 95000, '2024-01-15 09:00:00'),
(2, 'Bob',     'Engineering', 88000, '2024-01-15 09:00:00'),
(3, 'Charlie', 'Sales',       72000, '2024-01-15 09:00:00');

employee_audit table (empty, will be populated by triggers):

id	employee_id	action	old_salary	new_salary	changed_at	changed_by

CREATE TABLE employee_audit (
    id SERIAL PRIMARY KEY,
    employee_id INT NOT NULL,
    action VARCHAR(20) NOT NULL,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at TIMESTAMP NOT NULL,
    changed_by VARCHAR(100) NOT NULL
);

orders table:

id	customer_id	amount	status	created_at
1	101	250.00	pending	2024-03-01 10:00:00
2	102	430.00	pending	2024-03-02 11:00:00

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

INSERT INTO orders (id, customer_id, amount, status, created_at) VALUES
(1, 101, 250.00, 'pending', '2024-03-01 10:00:00'),
(2, 102, 430.00, 'pending', '2024-03-02 11:00:00');

customer_stats table:

customer_id	total_orders	total_spent	last_order_date
101	1	250.00	2024-03-01
102	1	430.00	2024-03-02

CREATE TABLE customer_stats (
    customer_id INT PRIMARY KEY,
    total_orders INT NOT NULL,
    total_spent DECIMAL(12,2) NOT NULL,
    last_order_date DATE NOT NULL
);

INSERT INTO customer_stats (customer_id, total_orders, total_spent, last_order_date) VALUES
(101, 1, 250.00, '2024-03-01'),
(102, 1, 430.00, '2024-03-02');

BEFORE vs AFTER Triggers

The timing of a trigger determines whether it runs before or after the actual data modification.

BEFORE Triggers

A BEFORE trigger fires before the database applies the change. The row has not been modified yet. This gives you the ability to:

• Inspect the incoming data before it is written
• Modify the incoming data (change column values before they are stored)
• Reject the operation entirely (by raising an exception or returning NULL)

BEFORE trigger fires
        ↓
Trigger can inspect, modify, or reject the data
        ↓
Database applies the INSERT/UPDATE/DELETE (or not, if rejected)

Best for: Validation, data normalization, auto-populating fields, enforcing business rules.

AFTER Triggers

An AFTER trigger fires after the database has already applied the change. The data modification is complete and visible within the transaction. This gives you the ability to:

• React to changes that have already happened
• Log the change to an audit table
• Update related tables based on the change
• Send notifications (via database-level mechanisms)

Database applies the INSERT/UPDATE/DELETE
        ↓
AFTER trigger fires
        ↓
Trigger can log, propagate, or react to the completed change

Best for: Audit logging, updating summary tables, cascading changes to related data.

BEFORE vs AFTER Summary

Aspect	BEFORE Trigger	AFTER Trigger
When	Before the data is modified	After the data is modified
Can modify the new row?	Yes	No (already written)
Can cancel the operation?	Yes (return NULL or raise exception)	Rollback only (raise exception to abort transaction)
Sees the change?	Sees proposed values (NEW)	Sees completed values (NEW)
Primary use	Validation, data transformation	Audit logging, synchronization

PostgreSQL Trigger Syntax

PostgreSQL uses a two-step approach: first create a trigger function, then attach it to a table with CREATE TRIGGER.

-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Trigger logic here
    -- Access OLD and NEW row values
    RETURN NEW;  -- or RETURN OLD, or RETURN NULL to cancel
END;
$$;

-- Step 2: Attach the trigger to a table
CREATE TRIGGER trigger_name
BEFORE | AFTER  INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function_name();

Accessing Old and New Values

Context	How to Access
New row (INSERT/UPDATE)	NEW.column_name
Old row (UPDATE/DELETE)	OLD.column_name
Modify new value (BEFORE)	NEW.column_name := value

Special Variables in Trigger Functions

PostgreSQL provides special variables inside trigger functions:

Variable	Description
NEW	The new row (available in INSERT and UPDATE triggers)
OLD	The old row (available in UPDATE and DELETE triggers)
TG_OP	The operation: 'INSERT', 'UPDATE', or 'DELETE'
TG_NAME	The name of the trigger
TG_TABLE_NAME	The table the trigger is attached to
TG_WHEN	'BEFORE' or 'AFTER'

FOR EACH ROW vs FOR EACH STATEMENT

Triggers can fire once per affected row or once per SQL statement.

FOR EACH ROW

The trigger fires once for every row affected by the statement. If an UPDATE modifies 100 rows, the trigger fires 100 times.

-- This fires once per row
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';
-- If 2 rows match, the trigger fires 2 times

This is the most common type.

FOR EACH STATEMENT

The trigger fires once per SQL statement, regardless of how many rows are affected.

CREATE OR REPLACE FUNCTION fn_log_bulk_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'A bulk change was made to %', TG_TABLE_NAME;
    RETURN NULL;  -- Return value is ignored for AFTER statement-level triggers
END;
$$;

CREATE TRIGGER trg_log_bulk_update
AFTER UPDATE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION fn_log_bulk_change();

Type	Fires	Access to OLD/NEW?	Use Case
FOR EACH ROW	Once per affected row	Yes	Row-level auditing, validation
FOR EACH STATEMENT	Once per SQL statement	No	Batch logging, summary notifications

Practical Trigger Examples

Example 1: Audit Logging (AFTER UPDATE)

The most common trigger use case: automatically record every salary change.

-- Step 1: Trigger function
CREATE OR REPLACE FUNCTION fn_audit_salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employee_audit (
        employee_id, action, old_salary, new_salary, changed_at, changed_by
    )
    VALUES (
        NEW.id, 'SALARY_CHANGE', OLD.salary, NEW.salary, NOW(), current_user
    );

    RETURN NEW;
END;
$$;

-- Step 2: Attach trigger (only fires when salary actually changed)
CREATE TRIGGER trg_audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION fn_audit_salary_change();

The WHEN clause means the trigger does not even fire if the salary did not change. This is more efficient than checking inside the function body.

Testing the Trigger

-- Give Alice a raise
UPDATE employees SET salary = 100000 WHERE id = 1;

-- Check the audit log
SELECT * FROM employee_audit;

Output:

id	employee_id	action	old_salary	new_salary	changed_at	changed_by
1	1	SALARY_CHANGE	95000.00	100000.00	2026-02-26 07:46:43.620137	admin

The audit entry was created automatically. No application code needed to remember to log it.

-- Update that doesn't change salary (trigger does NOT fire)
UPDATE employees SET department = 'Management' WHERE id = 1;

-- Check: no new audit entry
SELECT COUNT(*) FROM employee_audit;
-- Returns: 1 (still just the salary change)

Example 2: Auto-Populate updated_at (BEFORE UPDATE)

Automatically set the updated_at timestamp whenever a row is modified:

CREATE OR REPLACE FUNCTION fn_set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_employees_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_set_updated_at();

Testing

-- Before update
SELECT name, salary, updated_at FROM employees WHERE id = 2;

name	salary	updated_at
Bob	88000.00	2026-02-26 07:47:40.413506

-- Update salary (updated_at will be set automatically)
UPDATE employees SET salary = 92000 WHERE id = 2;

-- After update
SELECT name, salary, updated_at FROM employees WHERE id = 2;

name	salary	updated_at
Bob	92000.00	2026-02-26 07:47:40.413506

The updated_at column was updated automatically by the BEFORE UPDATE trigger, even though the UPDATE statement did not mention it.

Reusable Trigger Functions

In PostgreSQL, the same trigger function can be attached to multiple tables, as long as they all have the column the function references. This is perfect for updated_at:

-- Attach the same function to multiple tables
CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_set_updated_at();

CREATE TRIGGER trg_customers_updated_at
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION fn_set_updated_at();

One function, reused everywhere.

Example 3: Validation (BEFORE INSERT)

Enforce a business rule that salaries must be within a valid range for each department:

CREATE OR REPLACE FUNCTION fn_validate_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_min DECIMAL(10,2);
    v_max DECIMAL(10,2);
BEGIN
    -- Set salary ranges by department
    CASE NEW.department
        WHEN 'Engineering' THEN v_min := 60000; v_max := 200000;
        WHEN 'Sales'       THEN v_min := 40000; v_max := 150000;
        WHEN 'Marketing'   THEN v_min := 45000; v_max := 140000;
        ELSE                    v_min := 30000; v_max := 100000;
    END CASE;

    IF NEW.salary < v_min OR NEW.salary > v_max THEN
        RAISE EXCEPTION 'Salary % is outside valid range (% - %) for department %',
            NEW.salary, v_min, v_max, NEW.department;
    END IF;

    RETURN NEW;
END;
$$;

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

Note that PostgreSQL allows combining INSERT OR UPDATE in a single trigger definition.

Testing

-- Valid salary: succeeds
INSERT INTO employees (id, name, department, salary, updated_at)
VALUES (4, 'Diana', 'Engineering', 85000, NOW());
-- ✅ Success

-- Invalid salary: rejected by trigger
INSERT INTO employees (id, name, department, salary, updated_at)
VALUES (5, 'Eve', 'Sales', 200000, NOW());
-- ❌ ERROR: Salary 200000 is outside valid range (40000 - 150000) for department Sales

The invalid insert was rejected before the row was written. The table remains unchanged.

Example 4: Maintaining Summary Tables (AFTER INSERT)

Automatically update customer_stats whenever a new order is placed or deleted:

CREATE OR REPLACE FUNCTION fn_update_customer_stats()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO customer_stats (customer_id, total_orders, total_spent, last_order_date)
        VALUES (NEW.customer_id, 1, NEW.amount, NEW.created_at::date)
        ON CONFLICT (customer_id) DO UPDATE
        SET total_orders    = customer_stats.total_orders + 1,
            total_spent     = customer_stats.total_spent + EXCLUDED.total_spent,
            last_order_date = EXCLUDED.last_order_date;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE customer_stats
        SET total_orders = total_orders - 1,
            total_spent  = total_spent - OLD.amount
        WHERE customer_id = OLD.customer_id;
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_update_customer_stats
AFTER INSERT OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_update_customer_stats();

Testing

-- Before: check current stats
SELECT * FROM customer_stats WHERE customer_id = 101;

customer_id	total_orders	total_spent	last_order_date
101	1	250.00	2024-03-01

-- Insert a new order
INSERT INTO orders (id, customer_id, amount, status, created_at)
VALUES (3, 101, 180.00, 'pending', '2024-03-15 14:00:00');

-- After: stats updated automatically
SELECT * FROM customer_stats WHERE customer_id = 101;

customer_id	total_orders	total_spent	last_order_date
101	2	430.00	2024-03-15

The summary table was updated automatically. No application code touched customer_stats.

-- Delete the order
DELETE FROM orders WHERE id = 3;

-- Stats are decremented automatically
SELECT * FROM customer_stats WHERE customer_id = 101;

customer_id	total_orders	total_spent	last_order_date
101	1	250.00	2024-03-15

Example 5: Preventing Deletion (BEFORE DELETE)

Prevent deletion of employees who are department managers:

-- First, create a departments table for this example
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT REFERENCES employees(id)
);

INSERT INTO departments (id, name, manager_id) VALUES
(1, 'Engineering', 1),
(2, 'Sales', 3);

CREATE OR REPLACE FUNCTION fn_prevent_manager_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM departments
        WHERE manager_id = OLD.id
    ) THEN
        RAISE EXCEPTION 'Cannot delete employee % (%): they are a department manager',
            OLD.id, OLD.name;
    END IF;

    RETURN OLD;
END;
$$;

CREATE TRIGGER trg_prevent_manager_delete
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_prevent_manager_delete();

Testing

-- Attempting to delete a manager
DELETE FROM employees WHERE id = 1;
-- ❌ ERROR: Cannot delete employee 1 (Alice): they are a department manager

-- Deleting a non-manager succeeds
DELETE FROM employees WHERE id = 2;
-- ✅ Success

Example 6: Full Audit Trail (INSERT, UPDATE, DELETE)

A comprehensive trigger that logs all changes to the employees table:

CREATE OR REPLACE FUNCTION fn_full_employee_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO employee_audit (employee_id, action, new_salary, changed_at, changed_by)
        VALUES (NEW.id, 'INSERT', NEW.salary, NOW(), current_user);
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_audit (
            employee_id, action, old_salary, new_salary, changed_at, changed_by
        )
        VALUES (NEW.id, 'UPDATE', OLD.salary, NEW.salary, NOW(), current_user);
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employee_audit (employee_id, action, old_salary, changed_at, changed_by)
        VALUES (OLD.id, 'DELETE', OLD.salary, NOW(), current_user);
        RETURN OLD;
    END IF;
END;
$$;

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

Now every change to the employees table is automatically recorded:

-- Insert
INSERT INTO employees (id, name, department, salary, updated_at)
VALUES (5, 'Diana', 'Sales', 68000, NOW());

-- Update
UPDATE employees SET salary = 72000 WHERE id = 5;

-- Delete
DELETE FROM employees WHERE id = 5;

-- Check the complete audit trail
SELECT * FROM employee_audit ORDER BY changed_at;

Output:

id	employee_id	action	old_salary	new_salary	changed_at	changed_by
1	5	INSERT		68000.00	2026-02-26 08:16:11.352051	admin
2	5	UPDATE	68000.00	72000.00	2026-02-26 08:16:11.353195	admin
3	5	DELETE	72000.00		2026-02-26 08:16:11.354108	admin

Every operation is captured with its old and new values. This audit trail is maintained regardless of whether the change came from an application, a SQL script, a database admin, or a migration tool.

Example 7: Conditional Trigger with WHEN Clause

PostgreSQL supports a WHEN clause on the trigger definition itself, so the trigger function is not even called unless the condition is met:

CREATE OR REPLACE FUNCTION fn_log_large_raise()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Large raise alert: employee % salary changed from % to %',
        NEW.name, OLD.salary, NEW.salary;

    INSERT INTO employee_audit (
        employee_id, action, old_salary, new_salary, changed_at, changed_by
    )
    VALUES (
        NEW.id, 'LARGE_RAISE', OLD.salary, NEW.salary, NOW(), current_user
    );

    RETURN NEW;
END;
$$;

-- Only fires when the raise is more than 10%
CREATE TRIGGER trg_large_raise_alert
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.10)
EXECUTE FUNCTION fn_log_large_raise();

Testing

-- Small raise (5%): trigger does NOT fire
UPDATE employees SET salary = 99750 WHERE id = 1;  -- 95000 * 1.05

-- Large raise (20%): trigger fires
UPDATE employees SET salary = 114000 WHERE id = 1;  -- 95000 * 1.20
-- NOTICE: Large raise alert: employee Alice salary changed from 99750 to 114000

Example 8: Column-Specific UPDATE Trigger

Fire a trigger only when specific columns are updated using the OF clause:

CREATE OR REPLACE FUNCTION fn_salary_change_notification()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Salary changed for %: % → %', NEW.name, OLD.salary, NEW.salary;
    RETURN NEW;
END;
$$;

-- Only fires when the salary column is in the SET clause
CREATE TRIGGER trg_salary_column_update
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_salary_change_notification();

-- This fires the trigger (salary is in the SET clause)
UPDATE employees SET salary = 95000 WHERE id = 1;
-- NOTICE: Salary changed for Alice: 114000 → 95000

-- This does NOT fire the trigger (salary is not in the SET clause)
UPDATE employees SET department = 'Management' WHERE id = 1;
-- No notice

The OF salary clause prevents the trigger from firing when other columns are updated.

Managing Triggers

Viewing Existing Triggers

-- List all triggers on a specific table
SELECT
    trigger_name,
    event_manipulation,
    action_timing,
    action_orientation,
    action_statement
FROM information_schema.triggers
WHERE event_object_table = 'employees'
ORDER BY trigger_name;

Output:

trigger_name	event_manipulation	action_timing	action_orientation	action_statement
trg_audit_salary	UPDATE	AFTER	ROW	EXECUTE FUNCTION fn_audit_salary_change()
trg_employees_updated_at	UPDATE	BEFORE	ROW	EXECUTE FUNCTION fn_set_updated_at()
trg_validate_salary	INSERT	BEFORE	ROW	EXECUTE FUNCTION fn_validate_salary()
trg_validate_salary	UPDATE	BEFORE	ROW	EXECUTE FUNCTION fn_validate_salary()

-- Alternatively, query the pg_trigger system catalog for more detail
SELECT
    t.tgname AS trigger_name,
    p.proname AS function_name,
    CASE
        WHEN t.tgtype & 2 > 0 THEN 'BEFORE'
        ELSE 'AFTER'
    END AS timing,
    t.tgenabled AS enabled
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
JOIN pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'employees'
  AND NOT t.tgisinternal
ORDER BY t.tgname;

Disabling and Enabling Triggers

Sometimes you need to temporarily disable triggers (e.g., during bulk data loads):

-- Disable a specific trigger
ALTER TABLE employees DISABLE TRIGGER trg_audit_salary;

-- Load data without audit logging...
COPY employees FROM '/tmp/bulk_data.csv' WITH (FORMAT csv);

-- Re-enable the trigger
ALTER TABLE employees ENABLE TRIGGER trg_audit_salary;

-- Disable ALL triggers on a table
ALTER TABLE employees DISABLE TRIGGER ALL;
-- Perform bulk operations...
ALTER TABLE employees ENABLE TRIGGER ALL;

-- Disable only USER triggers (keeps system/constraint triggers active)
ALTER TABLE employees DISABLE TRIGGER USER;
ALTER TABLE employees ENABLE TRIGGER USER;

caution

Disabling triggers requires ALTER TABLE privileges. Always re-enable triggers in the same transaction or script to avoid leaving them disabled accidentally:

BEGIN;
    ALTER TABLE employees DISABLE TRIGGER USER;

    -- Bulk operation here
    INSERT INTO employees
    SELECT * FROM staging_employees;

    ALTER TABLE employees ENABLE TRIGGER USER;
COMMIT;

Dropping Triggers

-- Drop a trigger
DROP TRIGGER trg_audit_salary ON employees;

-- Drop only if it exists (avoids error)
DROP TRIGGER IF EXISTS trg_audit_salary ON employees;

-- Note: this drops the trigger but NOT the trigger function.
-- Drop the function separately if no longer needed:
DROP FUNCTION IF EXISTS fn_audit_salary_change();

Modifying Triggers

PostgreSQL does not support ALTER TRIGGER for changing the trigger body. However, because triggers reference a separate function, you can update the logic by simply replacing the function:

-- Update the trigger logic by replacing the function
CREATE OR REPLACE FUNCTION fn_audit_salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Updated logic: now also logs the department
    INSERT INTO employee_audit (
        employee_id, action, old_salary, new_salary, changed_at, changed_by
    )
    VALUES (
        NEW.id,
        'SALARY_CHANGE in ' || NEW.department,
        OLD.salary,
        NEW.salary,
        NOW(),
        current_user
    );

    RETURN NEW;
END;
$$;
-- The trigger automatically uses the updated function.
-- No need to drop and recreate the trigger itself.

If you need to change the trigger's timing, event, or WHEN clause, you must drop and recreate the trigger:

-- Change from AFTER to BEFORE requires drop and recreate
DROP TRIGGER trg_audit_salary ON employees;

CREATE TRIGGER trg_audit_salary
BEFORE UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION fn_audit_salary_change();

You can rename a trigger without dropping it:

ALTER TRIGGER trg_audit_salary ON employees RENAME TO trg_salary_audit_log;

Trigger Execution Order

When multiple triggers are defined on the same table for the same event and timing, PostgreSQL fires them in alphabetical order by trigger name:

-- These fire in alphabetical order: trg_a first, then trg_b
CREATE TRIGGER trg_a_audit
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_full_employee_audit();

CREATE TRIGGER trg_b_stats
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_update_department_stats();

If execution order matters, name your triggers deliberately:

-- Use numbered prefixes to control order
CREATE TRIGGER trg_01_validate ...;    -- fires first
CREATE TRIGGER trg_02_transform ...;   -- fires second
CREATE TRIGGER trg_03_audit ...;       -- fires third

Caution: Trigger Overuse

Triggers are powerful, but they are also one of the most frequently abused features in SQL. Excessive or poorly designed triggers create systems that are difficult to understand, debug, and maintain.

Problem 1: Hidden Logic

Triggers execute invisibly. A developer looking at an UPDATE statement has no idea that three triggers are firing behind the scenes, modifying other tables, sending notifications, and enforcing rules. This hidden behavior makes debugging extremely difficult.

-- A developer writes this simple update:
UPDATE employees SET salary = 100000 WHERE id = 1;

-- But behind the scenes:
-- Trigger 1: Logs to employee_audit
-- Trigger 2: Validates against salary ranges
-- Trigger 3: Updates department_budget summary
-- Trigger 4: Sends notification to HR system
-- Trigger 5: Recalculates tax withholding
-- Developer has NO IDEA any of this is happening

Problem 2: Cascading Triggers

A trigger on table A modifies table B. A trigger on table B modifies table C. A trigger on table C modifies table A. You now have an infinite loop or, at best, a deeply nested cascade that is impossible to trace.

employees trigger → updates department_stats
department_stats trigger → updates company_totals
company_totals trigger → updates executive_dashboard
executive_dashboard trigger → updates notification_queue
notification_queue trigger → ...where does it end?

PostgreSQL will raise an error if recursion exceeds the stack depth:

-- PostgreSQL will error if trigger recursion goes too deep
-- ERROR: stack depth limit exceeded
-- HINT: Increase the configuration parameter "max_stack_depth"

Problem 3: Performance Impact

Triggers add overhead to every affected write operation. A trigger that performs a complex query fires on every single row modification. With bulk operations, this compounds dramatically.

-- This update affects 50,000 rows
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';
-- If the AFTER UPDATE trigger does 3 INSERTs per row...
-- That's 150,000 additional INSERT operations!
-- What was a 200ms UPDATE becomes a 45-second operation

Problem 4: Difficult Testing

Triggers cannot be easily unit-tested in isolation. They fire automatically during data modifications, making it hard to test them without modifying real data and checking for side effects.

Problem 5: Debugging Nightmares

When something goes wrong, tracing the issue through a chain of triggers is far harder than tracing through application code with a debugger, stack traces, and logging.

Guidelines for Responsible Trigger Use

When to Use Triggers ✅

• Audit logging (record who changed what)
• Auto-populating timestamps (updated_at, created_at)
• Simple validation that CHECK constraints can't express
• Maintaining denormalized counters/totals for critical data
• Enforcing rules that MUST apply regardless of access method

When to AVOID Triggers ❌

• Complex business logic (use stored procedures or app code)
• External API calls or notifications (use app code or job queues)
• Logic that depends on application context (user session, etc.)
• Cascading changes across many tables (use explicit transactions)
• Anything that could be a simple CHECK or FOREIGN KEY constraint
• Performance-sensitive hot paths with bulk operations

The Golden Rule of Triggers

A trigger should be simple, focused, and unsurprising. If you need to read the trigger body to understand what a basic INSERT statement does, your trigger is doing too much. A developer should be able to look at a table and predict approximately what happens when they modify it, even if they do not know the exact trigger implementation.

Ask yourself: "If a new developer on my team runs this UPDATE, will they be surprised by what happens?" If the answer is yes, the trigger is doing too much.

Alternatives to Triggers

Instead of Trigger	Use This
Complex validation	Stored procedure that validates and inserts
External notifications	Application code or event queue (LISTEN/NOTIFY)
Cascading updates across many tables	Explicit transaction in application code
Simple column constraints	CHECK constraint or DEFAULT
Referential integrity	FOREIGN KEY with ON DELETE CASCADE
created_at timestamps	Column DEFAULT

-- Instead of a trigger for created_at, use a DEFAULT:
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
-- No trigger needed for created_at!
-- (updated_at still benefits from a trigger since DEFAULT only applies on INSERT)

-- Instead of a trigger for simple validation, use CHECK constraints:
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL CHECK (salary >= 0 AND salary <= 1000000),
    email VARCHAR(100) CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
-- No trigger needed for these validations!

-- Instead of a trigger for notifications, use PostgreSQL LISTEN/NOTIFY:
-- Application code listens for events:
LISTEN salary_changes;

-- In your update procedure (not a trigger):
NOTIFY salary_changes, '{"employee_id": 1, "new_salary": 100000}';

Common Mistakes to Avoid

Mistake 1: Forgetting RETURN in BEFORE Triggers

-- ❌ WRONG: Missing RETURN statement
CREATE OR REPLACE FUNCTION fn_bad_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    -- No RETURN statement!
    -- The INSERT/UPDATE will silently do nothing (row is NOT inserted/updated)
END;
$$;

A BEFORE trigger function must return NEW (for INSERT/UPDATE) or OLD (for DELETE) to allow the operation to proceed. Returning NULL cancels the operation silently.

-- ✅ CORRECT
CREATE OR REPLACE FUNCTION fn_good_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;  -- Required! Allows the operation to proceed
END;
$$;

Mistake 2: Not Handling NULL Values

-- ❌ WRONG: Comparing with != when OLD value might be NULL
IF OLD.salary != NEW.salary THEN  -- NULL != 95000 evaluates to NULL (not TRUE)
    -- This audit entry is MISSED when old salary was NULL
END IF;

-- ✅ CORRECT: Use IS DISTINCT FROM
IF OLD.salary IS DISTINCT FROM NEW.salary THEN  -- Handles NULLs correctly
    -- Audit entry is always created when values differ, even with NULLs
END IF;

-- Or use the WHEN clause on the trigger definition:
CREATE TRIGGER trg_audit
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION fn_audit();

Mistake 3: Creating Triggers That Fire on Irrelevant Changes

-- ❌ WRONG: Trigger fires on ANY update, even if salary didn't change
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_audit_salary();
-- Logs an "audit" entry even when only the name or department changed!

Fix: Use the WHEN clause or UPDATE OF to restrict when the trigger fires:

-- ✅ CORRECT option 1: WHEN clause
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION fn_audit_salary();

-- ✅ CORRECT option 2: UPDATE OF clause
CREATE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_audit_salary();

-- ✅ BEST: Combine both for maximum precision
CREATE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION fn_audit_salary();

Mistake 4: Modifying the Triggering Table in an AFTER Trigger

-- ❌ WRONG: Updating the same table in an AFTER trigger can cause recursion
CREATE OR REPLACE FUNCTION fn_bad_after_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- This UPDATE fires the trigger again → infinite loop!
    UPDATE employees SET updated_at = NOW() WHERE id = NEW.id;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_bad
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_bad_after_trigger();
-- ERROR: stack depth limit exceeded

Fix: Use a BEFORE trigger to modify the same row:

-- ✅ CORRECT: Use BEFORE to modify the same row
CREATE OR REPLACE FUNCTION fn_good_before_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();  -- Modifies the row being written, no extra UPDATE
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_good
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_good_before_trigger();

Mistake 5: Heavy Logic in Row-Level Triggers

-- ❌ WRONG: Complex aggregation query runs once PER ROW
CREATE OR REPLACE FUNCTION fn_heavy_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- This full table scan runs for EVERY updated row
    UPDATE department_stats
    SET avg_salary = (
        SELECT AVG(salary) FROM employees WHERE department = NEW.department
    )
    WHERE department = NEW.department;

    RETURN NEW;
END;
$$;
-- If UPDATE affects 10,000 rows → 10,000 full aggregation queries!

Fix: Use a statement-level trigger or handle in application code:

-- ✅ BETTER: Statement-level trigger runs once regardless of row count
CREATE OR REPLACE FUNCTION fn_refresh_dept_stats()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Runs only once, refreshes all department stats
    INSERT INTO department_stats (department, avg_salary, employee_count)
    SELECT department, AVG(salary), COUNT(*)
    FROM employees
    GROUP BY department
    ON CONFLICT (department) DO UPDATE
    SET avg_salary = EXCLUDED.avg_salary,
        employee_count = EXCLUDED.employee_count;

    RETURN NULL;  -- Return value is ignored for statement-level triggers
END;
$$;

CREATE TRIGGER trg_refresh_dept_stats
AFTER UPDATE OF salary ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION fn_refresh_dept_stats();

Summary

SQL triggers are automatic, event-driven code blocks that fire in response to data modifications:

• PostgreSQL uses a two-step approach: create a trigger function, then attach it with CREATE TRIGGER. This separation allows you to reuse one function across multiple tables and update logic with CREATE OR REPLACE FUNCTION without recreating triggers.
• BEFORE triggers fire before the change, allowing you to validate, modify, or reject the incoming data. Use them for input validation, auto-populating fields, and data normalization.
• AFTER triggers fire after the change is applied, allowing you to react to completed modifications. Use them for audit logging, maintaining summary tables, and cascading changes.
• Triggers respond to INSERT, UPDATE, and DELETE events. PostgreSQL allows combining events in one trigger: BEFORE INSERT OR UPDATE OR DELETE.
• Access old and new values with OLD and NEW. Use TG_OP to determine which operation fired the trigger.
• FOR EACH ROW triggers fire once per affected row. FOR EACH STATEMENT triggers fire once per SQL statement regardless of row count.
• Use the WHEN clause to filter trigger execution at the definition level and UPDATE OF to restrict to specific columns.
• Multiple triggers on the same table and event fire in alphabetical order by name.

Most importantly, use triggers responsibly:

• Keep them simple and focused on a single responsibility
• Prefer constraints and application code for complex logic
• Avoid cascading triggers that modify multiple tables in chains
• Always document triggers so other developers know they exist
• Consider performance impact on bulk operations

The Right Amount of Triggers

Most well-designed databases have a small number of triggers for critical, cross-cutting concerns: audit logging, updated_at timestamps, and perhaps one or two essential validation rules. If you find yourself with more than 3 to 4 triggers per table, step back and consider whether the logic belongs in stored procedures or application code instead.