Our Triggers

So what exactly do we need to happen when an INSERT or DELETE statement occurs on the comments table? Well we need the respective triggers (one for each statement) to query the comments table and COUNT the number of comments that belong to the user. From there then cast our own UPDATE statement on the users table, updating the user's num_comments column.

Two triggers, the same action. If you know me then you know I advocate DRY code wherever possible. Don't know what DRY means? It's simple really: Don't Repeat Yourself. And how do we prevent ourselves from repeating? Procedures and functions.

In this case we're going to write a helper procedure that will be called by both triggers. We'll write this now so that the triggers are valid when you type them up.

So what do we know that is pertinent to the procedure? Well we know we're counting comments belonging to a user. And we know we're updating a user's row in the user table. Sounds like we need the user's ID for this procedure to work, don't you agree? And actually, that's all we need.

CREATE PROCEDURE count_comments (IN userId INT)
BEGIN
 SET @numComments = (SELECT COUNT(*) FROM comments WHERE uid = userId);
 UPDATE users SET num_comments = @numComments WHERE uid = userId;
END;

This code shouldn't be much of a surprise to you. If it is I encourage you to read through my guide on MySQL stored routines. But a basic run down is in order anyways. On the first line we declare the procedure, nothing amazing here. You'll notice our one parameter, the user ID we're working with. It's an INT in the user table, so it's an INT here too. And we aren't outputting it, so it's of the type IN. Line three is where we collect the number of comments belonging to the given user ID. You should remember that the @ operator is how we reference variables in MySQL. Here we're setting the @numComments variable to be equal to the output from the SELECT query. That query will simply count the comments who have a uid of userId. And then of course we update the user's row accordingly. Remember that if you're on the command line you'll need to change your delimiter prior to writing the procedure, and then terminate it accordingly. Now that we've written the workhorse for the triggers, let's write the triggers themselves. Because they're nearly identical I am going to present them to you at once. We'll go through them afterwards.

CREATE TRIGGER update_num_comments_on_insert
 AFTER INSERT ON comments
 FOR EACH ROW
BEGIN
 CALL count_comments(NEW.uid);
END;

CREATE TRIGGER update_num_comments_on_delete
 AFTER DELETE ON comments
 FOR EACH ROW
BEGIN

That should all look fairly familiar to you now. We're giving our triggers descriptive names as they need to be unique and should be representative of what they're doing; or at least what they're reacting to. We're going with AFTER because we want to make sure that the action actually takes place; we're not interested in changing any of the incoming data.

So we create the trigger, set it to AFTER and declare the event that we're looking at. INSERT and DELETE in our case. Both need to happen on the comments table for us to care. And once all conditions are met, we CALL the procedure. This bit does need some explaining.

When an INSERT or UPDATE is performed we're given a variable called NEW. And inversely, when a DELETE is performed we get the variable OLD. These variables represent the data set that is being altered or saved. When an INSERT happens the uid field is always fed, to identify the user the commend belongs to. Thus NEW.uid refers to the uid of the new INSERT. And the same is true of OLD, except it is referring to the now-deleted data set.