Postgres in depth · Chapter 36 of 65

Triggers

By the end of this chapter you can make the database run your code whenever rows change: fix a row before it is written, record history, keep a counter right, and tell other programs that something happened. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: The Postgres flavour
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

Sana keeps finding students who cannot log in because their email was stored as Riya.Sen@Example.COM with a capital and a space, by one of the three apps that write to students. Kabir changed prices for the sale and nobody can say afterwards who changed what. The course cards show "students enrolled", and counting enrollments on every page view is getting slow. And the email service that sends receipts asks the database "any new paid orders?" every second, all day.

Each app could do the right thing itself, but each one would have to remember, and the next script someone writes will not. A function (chapter 35) only runs when someone calls it. A trigger runs it automatically, on every INSERT, UPDATE or DELETE, from every app.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    clean-email.sql: emails stored one way

    Whatever app writes to students, an email must be stored in lower case with no spaces around it. Add a trigger so that every INSERT, and every UPDATE that sets email, stores lower(trim(email)).

    Existing rows stay as they are, and updates of other columns keep working.

  2. Task 2

    price-log.sql: who changed the price

    Create a table price_changes with the columns id (bigint, an identity primary key), course_id (bigint, references courses), old_price and new_price (numeric(10, 2)) and changed_by (text, the role that made the change, filled in by default with current_user).

    Then add a trigger that writes one row for every course whose price really changes. An UPDATE that sets the price to the value it already had, or changes only other columns, logs nothing.

  3. Task 3

    student-count.sql: a counter that stays right

    Add a column students_count (integer, NOT NULL, default 0) to courses, fill it with each course's current number of enrollments, and keep it right with a trigger on enrollments when enrollments are inserted or deleted.

    The Check inserts many enrollments in one statement, deletes many, and deletes a student whose enrollments go with them.

  4. Task 4 · interview question

    Interview: notify-paid.sql, tell the receipt service

    The receipt service listens on the channel order_paid. Add a trigger on orders that sends a notification on order_paid, with the order's id as the payload (text), whenever an order becomes paid: inserted with status paid, or updated from another status to paid.

    An order that was already paid and is updated again sends nothing; failed and refunded orders send nothing.

What you will learn

  • Trigger functions and CREATE TRIGGER: RETURNS trigger, NEW, OLD, TG_OP.
  • BEFORE and AFTER, row and statement: when a trigger runs, and what it can do there.
  • WHEN and UPDATE OF: running only when it matters.
  • Audit logs and cached counters: the two classic uses, and their costs.
  • LISTEN and NOTIFY: messages to other sessions, delivered at commit.

More in The Postgres flavour

Build "Triggers" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.