lesson 65 Database engineering · advanced

Triggers & visible audit effects

Observe how triggers record changes in an audit table.

the idea

A trigger runs automatically after a matching event. SQLite row triggers use OLD and NEW values. They can record an audit trail but also create hidden side effects. SQL Server triggers are statement-level and use inserted/deleted tables, so their code is different.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

CREATE TRIGGER price_audit AFTER
UPDATE OF price ON lab_items WHEN OLD.price<>NEW.price
BEGIN
INSERT INTO lab_audit VALUES(NEW.id,OLD.price,NEW.price);
END;
UPDATE lab_items
SET price=price+5
WHERE id=1;
SELECT item_id,old_price,new_price
FROM lab_audit;

Watch for: A trigger’s side effect is part of the operation. Inspect and test it instead of assuming only one table changed.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Read prices the trigger will watch. A trigger fires on a data change. Note current prices; an UPDATE that changes price should write an audit row.

   step-1.sql Run

   sql›

   SELECT id, name, price
   FROM lab_items
   ORDER BY id;

2. 2

   Define a trigger and see its effect. AFTER UPDATE OF price inserts into an audit table when OLD.price differs from NEW.price.

   step-2.sql Run

   sql›

   CREATE TRIGGER price_audit AFTER
   UPDATE OF price ON lab_items WHEN OLD.price<>NEW.price
   BEGIN
   INSERT INTO lab_audit VALUES(NEW.id,OLD.price,NEW.price);
   END;
   UPDATE lab_items
   SET price=price+5
   WHERE id=1;
   SELECT item_id,old_price,new_price
   FROM lab_audit;