Trigger Basics

A trigger is a function that PostgreSQL calls automatically when a specific event happens on a table -- an INSERT, UPDATE, DELETE, or TRUNCATE. Triggers let you enforce complex business rules, maintain derived data, and record audit history without relying on application code.

How Triggers Work

The trigger system has two parts:

1. Trigger function -- a special function with RETURNS TRIGGER that contains the logic.
2. Trigger definition -- a CREATE TRIGGER statement that binds the function to a table and event.

When the event occurs, PostgreSQL invokes the trigger function automatically.

Creating a Trigger Function

A trigger function looks like a normal PL/pgSQL function, except:

• It takes no parameters (data comes through special variables).
• It returns the special type TRIGGER.

CREATE FUNCTION my_trigger_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- trigger logic here
  RETURN NEW;  -- for BEFORE triggers on INSERT/UPDATE
END;
$$;

Creating a Trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | TRUNCATE}
ON table_name
[FOR EACH {ROW | STATEMENT}]
[WHEN (condition)]
EXECUTE FUNCTION trigger_function_name();

Complete Example

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

-- Step 2: Attach it to a table
CREATE TRIGGER trg_employees_updated
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Now every time a row in employees is updated, the updated_at column is automatically set to the current timestamp.

BEFORE vs. AFTER

BEFORE Triggers

• Fire before the actual data change.
• Can modify the row being inserted or updated (by changing NEW).
• Can cancel the operation by returning NULL.
• Use for: validation, setting default values, modifying data before it is written.

CREATE FUNCTION validate_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.salary < 0 THEN
    RAISE EXCEPTION 'Salary cannot be negative: %', NEW.salary;
  END IF;

  IF NEW.salary > 500000 THEN
    RAISE NOTICE 'Capping salary at 500000 for %', NEW.name;
    NEW.salary := 500000;
  END IF;

  RETURN NEW;  -- must return NEW to proceed
END;
$$;

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

AFTER Triggers

• Fire after the data change is complete.
• Cannot modify the row (the write has already happened).
• Can see the final state of the row.
• Use for: logging, notifications, updating related tables.

CREATE FUNCTION log_new_employee()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO employee_log(employee_id, action, logged_at)
    VALUES (NEW.id, 'HIRED', NOW());
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_log_hire
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_new_employee();

FOR EACH ROW vs. FOR EACH STATEMENT

FOR EACH ROW

The trigger function runs once per affected row. If an UPDATE changes 100 rows, the function executes 100 times. The special variables NEW and OLD are available.

FOR EACH STATEMENT

The trigger function runs once per SQL statement, regardless of how many rows are affected. NEW and OLD are not available (there is no single row to reference).

CREATE FUNCTION log_bulk_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_log(table_name, action, logged_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
  RETURN NULL;  -- return value is ignored for AFTER triggers
END;
$$;

CREATE TRIGGER trg_log_bulk
AFTER UPDATE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_update();

Special Trigger Variables

Inside a trigger function, PostgreSQL provides several special variables:

Using TG_OP for Multi-Event Triggers

A single trigger function can handle multiple events:

CREATE FUNCTION employee_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log(table_name, action, new_data, logged_at)
      VALUES (TG_TABLE_NAME, 'INSERT', ROW_TO_JSON(NEW), NOW());
    RETURN NEW;

  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log(table_name, action, old_data, new_data, logged_at)
      VALUES (TG_TABLE_NAME, 'UPDATE', ROW_TO_JSON(OLD), ROW_TO_JSON(NEW), NOW());
    RETURN NEW;

  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log(table_name, action, old_data, logged_at)
      VALUES (TG_TABLE_NAME, 'DELETE', ROW_TO_JSON(OLD), NOW());
    RETURN OLD;
  END IF;

  RETURN NULL;
END;
$$;

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

The WHEN Clause

You can add a WHEN condition to fire the trigger only when certain criteria are met. This is evaluated before the trigger function is called, so it is more efficient than checking inside the function:

-- Only fire when salary actually changes
CREATE TRIGGER trg_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION notify_salary_change();

-- Only fire for high-value orders
CREATE TRIGGER trg_large_order
AFTER INSERT ON orders
FOR EACH ROW
WHEN (NEW.total_price > 1000)
EXECUTE FUNCTION alert_large_order();

The WHEN clause can reference OLD and NEW but cannot contain subqueries.

UPDATE OF Specific Columns

You can restrict an UPDATE trigger to fire only when specific columns change:

CREATE TRIGGER trg_email_changed
BEFORE UPDATE OF email ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_email_change();

This trigger only fires when the email column is included in the UPDATE's SET clause.

Managing Triggers

List Triggers on a Table

SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'employees';

Disable and Enable

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

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

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

Drop a Trigger

DROP TRIGGER IF EXISTS trg_employee_audit ON employees;

Note: dropping the trigger does not drop the trigger function. You must drop the function separately if desired.

Execution Order

When multiple triggers exist on the same table for the same event and timing:

1. BEFORE STATEMENT triggers fire first.
2. BEFORE ROW triggers fire for each row (alphabetically by trigger name).
3. The actual data operation happens.
4. AFTER ROW triggers fire for each row (alphabetically by trigger name).
5. AFTER STATEMENT triggers fire last.

Try It Yourself

Think through these scenarios (or try them on your own database):

1. Write a trigger that automatically lowercases the email column on INSERT and UPDATE.
2. Write a BEFORE DELETE trigger that prevents deleting an employee if they are still active (is_active = TRUE).
3. Write a statement-level trigger that logs the total number of rows affected by any DELETE on the orders table.

Summary