0Pricing
SQL Academy · Lesson

Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW

Understand trigger timing, granularity (row vs statement), and what NEW and OLD mean inside a trigger function.

Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What a Trigger Is

A trigger is a function the database calls automatically before or after an INSERT, UPDATE, DELETE, or TRUNCATE on a table.

Two Pieces: Function + Trigger

You write a function that returns trigger, then attach it to a table:

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

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

BEFORE vs AFTER

  • BEFORE — fires before the row is written; can modify NEW
  • AFTER — fires after the write; for side effects (audit, notifications)

FOR EACH ROW vs FOR EACH STATEMENT

  • FOR EACH ROW — runs once per affected row; NEW and OLD available
  • FOR EACH STATEMENT — runs once per statement, no per-row access

NEW and OLD

Inside a row trigger:

  • NEW — the new row (INSERT, UPDATE)
  • OLD — the previous row (UPDATE, DELETE)

Return Value Matters

For BEFORE row triggers:

  • Return NEW to proceed with that row
  • Return modified NEW to change what gets stored
  • Return NULL to cancel the operation for that row

WHEN Filter

Conditional fire:

CREATE TRIGGER trg_log_status
AFTER UPDATE OF status ON orders
FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_status_change();

OF column

BEFORE/AFTER UPDATE can fire only when specific columns change:

CREATE TRIGGER ... AFTER UPDATE OF status ON orders ...

Multiple Triggers

Triggers fire in alphabetical order by trigger name. Use prefixes (00_, 10_, ...) to control order.

Trigger Performance

Row triggers fire per row — heavy logic inside a row trigger × big DML = slow. Prefer statement triggers for bulk side effects.

Disabling Triggers

Temporarily skip:

ALTER TABLE orders DISABLE TRIGGER trg_audit;
-- bulk load...
ALTER TABLE orders ENABLE TRIGGER trg_audit;

-- Disable ALL triggers (use carefully):
ALTER TABLE orders DISABLE TRIGGER ALL;

Trigger Recursion

A trigger UPDATE on the same table fires the trigger again. Use guards (pg_trigger_depth()) or design carefully.

Recap

Triggers automate per-row reactions.

  • Function returning TRIGGER + CREATE TRIGGER
  • BEFORE for modifying, AFTER for side effects
  • NEW/OLD for row access
  • WHEN and OF column for selectivity

Quick Check

You want to automatically update an updated_at column on every row change. Which trigger setup?

Frequently asked questions

Is the “Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW” lesson free?

Yes — the full text of “Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW”?

Understand trigger timing, granularity (row vs statement), and what NEW and OLD mean inside a trigger function. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW
  2. PL/pgSQL Function Basics
  3. DO Blocks and Anonymous Code
  4. Auditing Tables with Triggers
← Back to SQL Academy