Triggers

A trigger is SQL that SQLite runs automatically whenever a specific event happens on a table — an insert, an update, or a delete — without your application code having to remember to do it every time. This lesson covers creating triggers, the NEW and OLD row references available inside them, and a worked example that keeps a recipes.updated_at timestamp current automatically.

CREATE TRIGGER

The basic shape is CREATE TRIGGER name AFTER (or BEFORE, or INSTEAD OF for views) an INSERT, UPDATE, or DELETE event ON a table, followed by a BEGIN ... END block containing the statements to run.

CREATE TRIGGER trg_recipes_touch_updated_at
AFTER UPDATE ON recipes
BEGIN
  UPDATE recipes
  SET updated_at = CURRENT_TIMESTAMP
  WHERE id = NEW.id;
END;

Now every plain UPDATE recipes SET ... WHERE id = ... automatically stamps updated_at too — the app never has to remember to set it manually, and it can't be forgotten in some code path.

-- The app only needs to write this...
UPDATE recipes SET prep_minutes = 50 WHERE id = 42;
-- ...and the trigger above quietly refreshes updated_at for that row too

NEW and OLD Row References

Inside a trigger body, NEW refers to the row's values after the triggering event, and OLD refers to its values before. Availability depends on the event: INSERT triggers only have NEW, DELETE triggers only have OLD, and UPDATE triggers have both.

-- Maintain a denormalized average rating on recipes whenever a review is added
CREATE TRIGGER trg_reviews_update_recipe_rating
AFTER INSERT ON reviews
BEGIN
  UPDATE recipes
  SET avg_rating = (
    SELECT AVG(rating) FROM reviews WHERE recipe_id = NEW.recipe_id
  )
  WHERE id = NEW.recipe_id;
END;

With that trigger in place, RecipeBox always has an up-to-date avg_rating to sort or filter by, without recalculating it at read time:

-- Just insert the review as normal...
INSERT INTO reviews (user_id, recipe_id, rating, comment)
VALUES (3, 42, 5, 'Best pie ever!');
-- ...and recipes.avg_rating for recipe 42 is automatically recalculated

BEFORE vs. AFTER

BEFORE triggers run before the triggering statement takes effect — useful for validating or adjusting NEW values before they're written. AFTER triggers (used above) run once the change has already happened — the natural choice when a trigger's job is to react to a change, like updating a related row.

-- BEFORE INSERT example: reject a rating outside 1-5 before it's ever written
CREATE TRIGGER trg_reviews_validate_rating
BEFORE INSERT ON reviews
BEGIN
  SELECT CASE
    WHEN NEW.rating NOT BETWEEN 1 AND 5
    THEN RAISE(ABORT, 'rating must be between 1 and 5')
  END;
END;