lesson 65 Database engineering · advanced

Triggers & visible audit effects

Observe how triggers record changes in an audit table.

the idea

A trigger runs statements automatically when a table changes. An AFTER UPDATE OF column trigger can record OLD and NEW values in an audit table, so every change leaves a trace. Use WHEN to log only real changes.

Dataset: Finance / Payments

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count cards by status. Run this. Most cards are active; lost, expired, and frozen show up as smaller groups—context for the status changes your trigger will log.

   step-1.sql Run

   sql›

   SELECT status, COUNT(*) AS cards
   FROM cards
   GROUP BY status;

2. 2

   Log status changes with a trigger. The AFTER UPDATE trigger inserts into status_log when you freeze card 1—you get an audit row without writing a separate INSERT yourself.the pattern

   example.sql Run

   sql›

   CREATE TABLE status_log (card_id INTEGER, changed_to TEXT);
   CREATE TRIGGER log_status AFTER
   UPDATE OF status ON cards
   BEGIN
   INSERT INTO status_log VALUES (NEW.card_id, NEW.status);
   END;
   UPDATE cards
   SET status = 'frozen'
   WHERE card_id = 1;
   SELECT *
   FROM status_log;

common mistake

A trigger fires once per affected row; an UPDATE touching 50 rows writes 50 audit rows.