Triggers

A stored procedure, as covered in the previous lesson, only runs when something explicitly CALLs it. A trigger is different: it's a named block of SQL that MySQL fires automatically whenever a specific kind of change - an INSERT, UPDATE, or DELETE - happens on a specific table. You never call a trigger yourself; it just happens, silently, as a side effect of the change that set it off.

BEFORE vs AFTER Triggers

A trigger can run either just before the triggering change is written, or just after it already exists in the table - and the two are suited to genuinely different jobs:

A Worked Example - Keeping review_count in Sync

Database Normalization touched on the idea of deliberately denormalizing a value for read performance - here's exactly that scenario. Suppose movies has a review_count column that's meant to always match the number of matching rows in reviews, so the site never has to count them on every page load. An AFTER INSERT trigger keeps that promise automatically, every time a review is added - anywhere, by any code path:

DELIMITER //

CREATE TRIGGER after_review_insert
AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
  UPDATE movies
  SET review_count = review_count + 1
  WHERE id = NEW.movie_id;
END//

DELIMITER ;

From this point on, a plain INSERT into reviews is all it takes - movies.review_count updates on its own, with no application code even aware it happened:

INSERT INTO reviews (user_id, movie_id, rating, comment, review_date)
VALUES (14, 5, 5, 'A genuine classic.', '2026-08-16');
-- movies.review_count for movie 5 is now incremented automatically

NEW and OLD - Referring to the Row Involved

Inside a trigger, NEW refers to the row as it will be (or just was) written - that's how NEW.movie_id above knows which movie's count to update, taken directly from the review that was just inserted. OLD is the counterpart available in UPDATE and DELETE triggers, referring to the row's values before the change - useful, for example, if you ever needed an AFTER DELETE trigger that decrements review_count using OLD.movie_id when a review is removed. INSERT triggers only ever have NEW (there's no "before" row to refer to), and DELETE triggers only ever have OLD (there's no "after" row left).

Viewing and Removing Triggers

SHOW TRIGGERS lists every trigger currently defined on the database, along with the table and timing it's attached to:

SHOW TRIGGERS;

Dropping one removes it entirely - the underlying table and its data are completely unaffected:

DROP TRIGGER after_review_insert;

A Caution About Overusing Triggers

Triggers are powerful precisely because they're invisible - and that's also exactly what makes them dangerous to overuse. An INSERT that looks completely simple in your application code might silently be updating an entirely different table, and there's nothing in that one line of application code to tell you so. Debugging becomes genuinely harder: something changes unexpectedly, and tracing it back means remembering to go check for triggers at all, since nothing in the calling code hints that one might exist.

This is the same underlying concern as automatic side-effect systems elsewhere in software - like Django's signals, if you've come across that pattern on this site's Django track - and the practical takeaway is the same: reserve triggers for a small number of well-understood, well-documented cases, like the single-purpose review_count example above, rather than scattering general business logic across triggers where it's easy to forget it's even running.