Triggers

Triggers are functions that automatically run in response to certain events on a table. They let you enforce business rules, maintain data consistency, and automate tasks without changing your application code. When something happens to a row, the trigger fires and executes your custom logic.

How Triggers Work

A trigger has three components:

• Event — What operation fires the trigger (INSERT, UPDATE, DELETE)
• Timing — When it fires (BEFORE or AFTER the operation)
• Function — The code that runs when the trigger fires

The function must be created first, then attached to the table with a trigger.

Creating a Trigger Function

First, create the function that the trigger will call:

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

The function returns TRIGGER type and uses NEW to refer to the row being inserted or updated. For UPDATE and DELETE triggers, you can also use OLD to reference the original row.

Attaching the Trigger

Now attach the function to a table:

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

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

Audit Log Trigger

A common use case is tracking changes to data:

CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name TEXT,
  record_id INTEGER,
  action TEXT,
  old_data JSONB,
  new_data JSONB,
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, record_id, action, new_data)
    VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data)
    VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));
  END IF;
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_students
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_changes();

This automatically logs every change to the students table with the old and new values. Very useful for compliance and debugging.

Managing Triggers

To disable a trigger without dropping it:

ALTER TABLE students DISABLE TRIGGER audit_students;

To re-enable it:

ALTER TABLE students ENABLE TRIGGER audit_students;

To remove a trigger:

DROP TRIGGER IF EXISTS audit_students ON students;

BEFORE vs AFTER Triggers

The timing matters:

• BEFORE — Runs before the operation. Can modify or cancel the row. Use for validation and default values.
• AFTER — Runs after the operation is committed. Use for logging, notifications, and cascading changes.