PostgreSQL Triggers

A trigger is a function that PostgreSQL automatically executes in response to a specific event on a table. When a row is inserted, updated, or deleted, a trigger can fire — running custom logic before or after the operation. Triggers are used for auditing, enforcing business rules, maintaining derived data, and logging changes automatically.

How Triggers Work

A trigger has two parts:

1. A trigger function: A PL/pgSQL function that contains the logic to execute. It must return TRIGGER as its return type.
2. A trigger definition: Specifies when to fire the function (BEFORE or AFTER), which event (INSERT, UPDATE, DELETE), and which table to watch.

Trigger Function Return Values

For row-level triggers:

• In a BEFORE trigger, returning NEW allows the operation to proceed with the (possibly modified) row. Returning NULL cancels the operation.
• In an AFTER trigger, the return value is ignored.

Two special variables are available inside trigger functions:

• NEW — the new row being inserted or updated
• OLD — the existing row before an update or delete

Creating a Trigger Function

CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- logic here
    RETURN NEW;  -- or OLD, or NULL
END;
$$;

Creating the Trigger

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

Example 1: Auto-Set a Timestamp on Update

A common need is to automatically record when a row was last updated.

-- Add an updated_at column
ALTER TABLE employees ADD COLUMN updated_at TIMESTAMP;

-- Create the trigger function
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

-- Create the trigger
CREATE TRIGGER trg_employees_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Every time an employee row is updated, the trigger sets updated_at to the current timestamp automatically — no application code required.

Example 2: Audit Log Trigger

An audit log records every salary change made to the employees table.

-- Create the audit table
CREATE TABLE salary_audit (
    id SERIAL PRIMARY KEY,
    employee_id INT,
    old_salary NUMERIC,
    new_salary NUMERIC,
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Create the trigger function
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$;

-- Create the trigger
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

After any update to the employees table, the trigger checks if the salary changed. If it did, a record is written to salary_audit with the old and new values.

-- Test the trigger
UPDATE employees SET salary = 90000 WHERE id = 2;

-- Check the audit log
SELECT * FROM salary_audit;

Example 3: BEFORE INSERT Trigger for Validation

A trigger can enforce a rule that the application cannot always guarantee — for example, preventing negative prices.

CREATE OR REPLACE FUNCTION prevent_negative_price()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative. Got: %', NEW.price;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_check_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_price();

RAISE EXCEPTION throws an error and cancels the INSERT or UPDATE. The % in the message is replaced with the actual value.

-- This will fail
INSERT INTO products (name, price) VALUES ('Pen', -5.00);
-- ERROR: Price cannot be negative. Got: -5

Statement-Level Triggers vs Row-Level Triggers

FOR EACH ROW fires the trigger once per affected row. FOR EACH STATEMENT fires the trigger once per SQL statement, regardless of how many rows were affected. Statement-level triggers cannot access NEW or OLD.

CREATE TRIGGER trg_log_bulk_delete
AFTER DELETE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_delete_event();

Viewing Triggers

-- In psql, describe the table
\d employees

-- Or query the information schema
SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'employees';

Dropping a Trigger

DROP TRIGGER trg_salary_audit ON employees;

Dropping a trigger does not drop the trigger function. The function can be reused by other triggers or dropped separately.

Enabling and Disabling Triggers

-- Disable a trigger temporarily
ALTER TABLE employees DISABLE TRIGGER trg_salary_audit;

-- Re-enable it
ALTER TABLE employees ENABLE TRIGGER trg_salary_audit;

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

Key Points

• A trigger automatically executes a function in response to INSERT, UPDATE, or DELETE events.
• The trigger function must return the TRIGGER data type.
• BEFORE triggers can modify or cancel the operation; AFTER triggers run after it completes.
• NEW holds the incoming row data; OLD holds the row's data before the change.
• FOR EACH ROW fires once per affected row; FOR EACH STATEMENT fires once per statement.
• RAISE EXCEPTION inside a trigger cancels the operation and returns an error.
• Triggers are powerful but should be used carefully — hidden side effects can make debugging difficult.