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:

 

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 (note: if one the command line remember to change your delimiter to something other than the semi-colon- I used || myself):

 

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.