Triggers

Explanation

A trigger is a rule that says: "when this event happens to this table, run this code automatically." Events are INSERT, UPDATE, or DELETE, and the trigger can fire BEFORE or AFTER the event, FOR EACH ROW or FOR EACH STATEMENT.

Common uses:

• Auditing changes (log old/new values to a history table)
• Enforcing business rules that are awkward in a constraint
• Keeping a derived/cached column in sync

Triggers often call a stored procedure (the "trigger function") to do the work.

Syntax

-- PostgreSQL style
CREATE TABLE salary_audit (
  emp_id INT,
  old_salary NUMERIC,
  new_salary NUMERIC,
  changed_at TIMESTAMP DEFAULT NOW()
);
 
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO salary_audit (emp_id, old_salary, new_salary)
  VALUES (OLD.id, OLD.salary, NEW.salary);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_salary
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION log_salary_change();

Interactive Example

Trigger languages aren't available here, but you can preview the data a salary audit trigger would capture: pairs of old and new salaries for a hypothetical raise.

Common Mistakes

• Forgetting row-vs-statement timing. FOR EACH ROW runs per changed row; FOR EACH STATEMENT runs once per statement — pick the right one.
• Trigger loops. A trigger that updates the same table can re-fire itself forever unless guarded.
• Silent performance hits. Every write now also runs the trigger; keep it lightweight.

Best Practices

• Use triggers for cross-cutting concerns (audit, sync) rather than core business logic you'd want in app code.
• Guard WHEN conditions so the trigger only fires when relevant.
• Document triggers prominently — they're invisible to readers of the main query.

Practice Question

Describe a trigger that, after any INSERT into orders, updates a last_order_date column on the matching customers row. Which event, timing, and granularity would you choose?

Summary

Triggers automatically execute SQL in response to table events, often by calling a stored procedure. They're powerful for auditing and syncing but can surprise developers and impact write performance, so use them sparingly.

FAQ