Introduction: What is a Trigger
For the uninitiated, a trigger is a rule that you put on a table which basically says, whenever you DELETE, UPDATE or INSERT something in this table, also do something else. For instance, we might want to log a change, but instead of writing two separate queries, one for the change, and one for the log, we can instead write a trigger that says, “Whenever this row is updated, create a new row in a different table to tell me that the update was made”. It adds a little overhead to the initial query, but since there are not two packets traveling to your database to do two separate things, there is an overall performance gain (in theory anyway).
Triggers were introduced into MySQL in version 5.0.2. The syntax for a trigger is a bit foreign on first blush. MySQL uses the ANSI SQL:2003 standard for procedures and other functions. If you are comfortable with a programming language in general, it is not that difficult to understand. The specification is not freely available, so I will do my best to use simple structures and explain what is happening within the trigger. You will be dealing with the same logic structures that any programming language provides.
As I mentioned above, triggers will be executed procedurally on UPDATE, DELETE and INSERT events. What I didn’t mention is that they can be executed either before or after the event defined. Therefore, you could have a trigger that will fire before a DELETE or after a DELETE, so on and so on. This means that you could have one trigger that fires before an INSERT and a separate one that fires AFTER an INSERT, which can be very powerful.
I am going to look at three uses that you could consider adding to your toolbox. There are several uses that I will not be delving into, as I feel there are better methods to get the same results, or they deserve their own tutorial. Each of these uses that I am exploring have a counterpart in your server side logic layer, and are not new concepts. However, as applications grow more and more complicated, the further we can abstract the layers of an application to handle what they should, the greater our internal development usability becomes.
Beginnings: My Table Structure, Tools and Notes
I am working with a mythical cart system, with items that have prices. I have tried to keep the data structure as simple as possible just for illustration purposes. I am naming columns and tables for the purpose of understanding, and not for production use. I am also using TIMESTAMPS rather than other alternatives for ease. For those playing the at-home version of today’s game, I am using the table names of carts, cart_items, cart_log, items, items_cost.
Please note throughout this tutorial I will be using very simple queries to express my points. I am not binding any variable, as I am not using any user input. I want to make the queries as easy to read as possible, but don’t use this tutorial for anything other than practical trigger applications. I know there might be a comment or two about this, so consider this my disclaimer.
I am using the Particle Tree PHP Quick Profiler to see execution times. I am also using the database abstraction layer provided in the tool just for my own benefit. It’s a nice tool, and does much more than just providing SQL execution times.
I am also using Chive to illustrate the DB effects and create my triggers. Chive is MySQL 5+ only, and is very similar to PHPMyAdmin. It’s prettier, but also much buggier at the moment. I am using Chive, simply because it gives good screen shots as to what is happening with the queries.
One other quick note. You may need to change the delimiter for MySQL while creating a trigger. The natural delimiter for MySQL is ; but since we will be using that delimiter for our added queries, you may need to explicitly rename the delimiter if creating these via command line. I have chosen not to show this, because using Chive, there is no need to change the delimiter.
To change a delimiter, you would simply do this before your trigger command:
1. DELIMITER $$
Full article...
