Welcome to Part 4 of the series on MySQL functions. You might want to check out Part 1 – Date and Time Functions, Part 2 – String Functions or Part 3 – Fun With Numbers, in case you have missed any of them. This time in Part 4, I’m not going to show you any of the built-in functions that MySQL comes with, instead I’m going to give a brief overview of how you can create your own functions.
Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.
Triggers
I’ll start with triggers first because they’re not quite as complicated as stored procedures. You could think of a trigger in the same way you would a JavaScript event. It’s a chunk of code which is run when a certain action takes place on a table in the database.
The actions that can cause a trigger to run are INSERT, UPDATE and DELETE. So whereas in JavaScript you might assign a function to a button’s onclick event, you can assign a trigger to a table’s INSERT event.
The main use of triggers I’ve found is to automate repetitive tasks which can’t be included as part of the query. For example, if you update table X, but whenever that happens you need something to happen on table Y, you might be able to use an UPDATE trigger to handle it for you.
Anatomy of a Trigger
The basic structure of a trigger looks like this:
DELIMITER // CREATE TRIGGER before_insert_tablename BEFORE UPDATE ON tablename FOR EACH ROW BEGIN ...your code goes here... END// DELIMITER ;
Let’s go through it line by line.
Line 1: We set the MySQL delimiter character to ‘//‘. By default the delimiter character is ‘;‘, but we will need to use this in the trigger’s code at the end of each line. So to prevent MySQL getting confused about where the trigger starts and finishes we override it with ‘//‘.
Line 2: After the CREATE TRIGGER statement we give the trigger a name. The best sort of name is one which describes as follow:
- When the trigger will run?
- What event will cause the trigger to run?
- The name of the table the trigger belongs to.
This will make it easier for you to find a specific trigger in future, and work out what it’s doing.
Line 3: Next we tell MySQL when we want the trigger to run, which event causes the trigger to fire, and which table we want the trigger to belong to. The two options we have for when the trigger runs are BEFORE and AFTER.
Line 4: Then we tell MySQL that the trigger should loop over each row being inserted, updated or deleted.
Lines 5 & 7: BEGIN and END signify where the trigger’s code will go.
Line 6: This is where the trigger code goes. You can run SQL queries, set and use MySQL session variables, and a few other programming staples like loops and if statements.
Line 8: Finally we reset MySQL’s delimiter character back to a semi-colon.
A Real Trigger Example
A couple of years ago, I was working as part of a small team on a sports video site. It started out simple enough but the client soon requested more and more features and functionality to be added.
This led to our main video retrieval query becoming bloated, because whenever a video was queried a large number of values, such as popularity rating, number of fans etc. had to be calculated.
Working out each of these values often involved a sub-query or join in the main video retrieval query. For this example I’ll use the video rating value as a simplified example.
The original query would have looked something like this (only a whole lot bigger!):
SELECT
video_id,
video_title,
(SELECT AVG(rating_score) FROM ratings WHERE rating_video = 1) AS video_rating
FROM
videos
WHERE
video_id = 1
And as you can see there’s a sub-query calculating the video’s current popularity rating.
To make the query faster we decided it would be best if we created some new fields in the videos table to contain the data, removing the need for the sub-query.
SELECT
video_id,
video_title,
video_rating
FROM
videos
WHERE
video_id = 1
Bye-bye sub-query, hello faster website.
But this of course meant we had to find some way of populating the extra fields, and more importantly, a way of keeping them up to date i.e. whenever someone rated a video.
We could have done this in the site’s PHP code but that might have involved a couple of new queries, and by this point the idea of adding SQL was not a popular one.
So our head of development decided we should use triggers to solve the problem. After all, they fitted the bill perfectly – we needed to perform an operation on the database every time a record was added to it (an AFTER INSERT event).
This is (roughly) what we ended up with:
DELIMITER //
CREATE TRIGGER after_insert_rating
AFTER INSERT ON ratings
FOR EACH ROW
BEGIN
SELECT AVG(rating_score) FROM ratings WHERE rating_video = NEW.rating_video INTO @new_avg_rating;
UPDATE videos SET video_rating = @new_avg_rating WHERE video_id = NEW.rating_video;
END//
DELIMITER ;
Quite a simple trigger, but one that worked quite well.
If you want to learn more about triggers, the MySQL Trigger Syntax manual page is the best place to start. It’s quite daunting at first, but take it slowly, check out some examples, and you’ll get it in no time.
Stored Procedures
Stored procedures, also known as stored routines or functions, are essentially like creating your own functions in PHP – except you’re using MySQL. You define the stored procedure, and can then call it from any of your queries in future.
The syntax for declaring a stored procedure in MySQL is very similar to the trigger above, so I’ll jump straight in with a practical example.
When creating the videos and ratings tables for the above example I decided to populate the ratings table with some dummy data. As some of you may know, creating dummy data when writing an application is mind-numbingly boring.
But rather than writing a PHP script to generate some dummy data I decided to kill two birds with one stone and use a stored procedure instead.
DELIMITER //
DROP PROCEDURE IF EXISTS PopulateRatings//
CREATE PROCEDURE PopulateRatings(IN DaysBack INT(2), IN MaxID INT(1), IN MaxRating INT(1))
BEGIN
DECLARE ratingDate DATE;
DECLARE videoID INT;
DECLARE videoRating INT;
DECLARE counter INT;
SET counter = 1;
WHILE counter <= 10 DO
SELECT CURDATE() - INTERVAL FLOOR( RAND() * DaysBack ) DAY INTO ratingDate;
SELECT 1 + FLOOR(RAND() * (MaxID - 1)) INTO videoID;
SELECT 1 + FLOOR(RAND() * (MaxRating - 1)) INTO videoRating;
INSERT INTO ratings VALUES(videoID, videoRating, ratingDate);
SET counter = counter + 1;
END WHILE;
END//
DELIMITER ;
The main things in the procedure to take note of are:
CREATE PROCEDURE...– This defines the procedure and tells MySQL that it will take three parameters. Note the use of the keywordINand that I’ve defined the type of the input variable. Another option would be to use theOUTkeyword. If you do so, and define that variable somewhere in the procedure, it will be returned at the end of execution.DECLARE– You can declare variables within the procedure. Don’t forget to specify the type of data the variable will store.SET– Assign a value to a variable.- You can do loops in stored procedures. The
WHILEloop is just one example, but you can see more here. - To retrieve values from SQL queries and store them in the variable I declared earlier, I’ve used
SELECT…INTOwhich takes the result of the query and places it into the variable specified. - Don’t forget to increment your counter variables or the procedure will loop forever! I did this on a test run and filled my table with 10K rows of randomness.
Conclusion
That about wraps it up for triggers and stored procedures. I hope you’ve found this article and series interesting and will be able to put it all to use in your next web application. Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.
