Automation Is Good
SQL triggers are an excellent way to limit overhead. Much of what we do when writing web applications is overhead. A decent amount of code in any web application is going to be written out of sheer necessity because of a previous action. This extra code isn’t productive, it’s not enjoyable and each time you have to write it you open up the possibility to human error. Thankfully there are ways to severely eliminate some amount of impact this mundane overhead has.
Throughout this guide we’re going to look at a specific scenario. We will address the issue and we’ll solve it accordingly. I don’t care how you follow along; this is not tied to any form of development, after all. Instead we’re going to do all of our testing through whatever medium you’re most comfortable with. I’m in my terminal, doing things from the command line. You may feel free to use phpMyAdmin if that’s your choice.
You run a site with a membership system. Articles posted to your site can be commented on; only members may comment. And naturally you won’t to keep track of how many comments each user has. You can do this any number of ways but the two most common are:
Either count the comments table, looking for rows where the user ID of the comment is the same as the profile being viewed. Or store the number of comments the user has in the users table. I prefer the latter and it’s what this guide is going to be written off. Number of comments is a detail belonging to the member, so it might as well be stored with the rest of their details in the user table.
Addressing The Issue
Our scenario has an issue that would soon become apparent once we began writing code. That is, it’s a pain to have to take care of two separate tables when dealing with the comments system. Every single time a new comment is created the user table needs to be updated. And every time a comment is deleted, the same thing has to occur.
Chances are this action would take the form of a method or function in your code, which you’d have to call every time you did an INSERT or DELETE. What if you or someone you’re working with forgets to call this update method? Humans are error prone and it’s not entirely out of the question that this mistake would be made. So let’s solve it.
This takes form of SQL triggers. A trigger is a stored routine, not unlike procedures or functions. But unlike procedures and functions, a trigger is called automatically. When it’s called depends on how you configure it. But you may configure a trigger to activate on any of the three following actions: INSERT, UPDATE and DELETE.
Triggers are table-specific. When writing a trigger you will declare the action that activates it (any of the three above) and the table on which that action needs to occur. Triggers may be used for any number of things, but they are commonly employed along side procedures and functions.
Let’s Get Trigger Happy
Knowing now what we do about triggers, can you guess how we’ll use them in our scenario? Any time a comment is created or delete we need to increment or decrement its author’s comment count accordingly. We’ll do this by writing triggers which react to INSERT and DELETE actions against the comments table.
In a more realistic application this wouldn’t be enough. Chances are comments wouldn’t ever be deleted, but rather just hidden from view. Thus a trigger for the UPDATE statement would also be necessary. But for the sake of introducing and explaining triggers, we’ll forego that momentarily.
Before we can really begin to appreciate triggers we’ll need a users and comments table. Feel free to write your own if you’d like, but I’ll be using the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
CREATE TABLE users ( uid INT NOT NULL AUTO_INCREMENT, username VARCHAR(10) NOT NULL DEFAULT '', num_comments INT NOT NULL DEFAULT 0, PRIMARY KEY(uid), UNIQUE username(username) ); CREATE TABLE comments ( cid INT NOT NULL AUTO_INCREMENT, uid INT NOT NULL DEFAULT 0, comment VARCHAR(200) NOT NULL DEFAULT '', PRIMARY KEY(cid), KEY uid(uid) );
If you do write your own tables just take note of the column names, as this guide will be written based on the above. You should also go ahead and insert a ficticious user into the users table.
As I stated before, triggers are activated when a certain action happens on the table it’s configured to look at. What I didn’t mention is that when a trigger activates in reaction to a statement may also be configured. Triggers will activate either BEFORE or AFTER the action is actually acted upon by MySQL. Which you go with when writing your triggers depends on what your trigger needs to do. If it needs to edit the incoming data from an INSERT prior to it being saved, then BEFORE is your best bet. If your trigger just wants to react to the save, then AFTER is a good choice. We’ll be going with the latter.
The basic syntax for a trigger is as follows:
CREATE TRIGGER name when event
ON table FOR EACH ROW body
Thus a bare-bones trigger might look a little like this:
1 2 3 4 5 6 7 8
DELIMITER || CREATE TRIGGER example_trigger AFTER INSERT ON random_table FOR EACH ROW BEGIN SET @someVar = (SELECT something FROM somewhere); END;||
Now of course this trigger does nothing but poll some table for a column. But it shows that the syntax is pretty straight forward.
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.
1 2 3 4 5 6 7
DELIMITER || 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
DELIMITER || 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 CALL count_comments(OLD.uid); END;||
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.
What Happens Now?
With our triggers now set we’re free to create and delete comments as we please. Do this however you please, I’ll be providing some SQL for you to use if you’re on the command line. But this will work fine if you use phpMyAdmin’s insert utility.
INSERT INTO comments (uid, comment) VALUES (1, 'A comment!'), (1, 'Another comment');
Insert a few rows into the comments table with the uid of the user you created earlier. Now look at their row in the users table.
SELECT * FROM users;
If all went according to plan their num_comments column will be equal to however many comments you created. Delete a comment now.
DELETE FROM comments WHERE uid = 1 LIMIT 1;
And again, look at the users table. Notice the num_comments column updated? Pretty damn simple, isn’t it?
We may not have written any application code in this guide but it should be very apparent how this would affect your code. With these triggers it would be unnecessary for us to write code which updates the user table every time a comment is created or delete. So while there was no application code in this guide, it still bares a direct effect on any code you would write.
I encourage you to continue familiarizing yourself with triggers. Your challenge is to mimic a more realistic situation where comments aren’t always deleted, but rather hidden from view. A BOOLEAN flag on each comment and an UPDATE trigger would suffice here. Share your results. And as always, if you have any questions or notice any problems in what you’ve read: ask.