Using Triggers in MySQL

First let’s take a look at what a Trigger is.

What is a Trigger?

A Trigger is a small (or large) MySQL command that is triggered when a specific set of conditions are met. For example you can tell MySQL that you would like a certain field updated should a new row be inserted into the table.

Why are they useful?

Triggers are useful in a lot of situations. Any situation where you would like the database to automatically do something when an event occurs. For example maybe you want to update a stock count when a product is bought. You would normally run the purchase code & then run the stock recalculation. Why not get MySQL to do it without having to use any PHP, or whatever Server Side language you may be using.

Can I have an example?

Let’s go back to our purchase & stock example as it’s quite easy to understand. Let’s say we have a products table that keeps track of stock, and a purchase table that holds a record that a customer has ordered a product.

An important note: If you are using PhpMyAdmin, please change the delimiter (small box below SQL window) before running this command or it may fail. More about it at the end of the tutorial.

This will remove the number of items the customer bought from the amount of stock remaining of that item. Let’s take a look at how it does it.

First we create a trigger and give it a name. This name is just an identifier, and allows you to delete it using the DROP TRIGGER command.

Next we tell the trigger when to run. In this case we want it to run every time a new row is inserted into the purchase table. FOR EACH ROW does exactly what it says, tells MySQL to run the trigger for each row inserted. BEGIN and respectively END start and end the block within which you write the commands to run when the trigger is activated.

In our case it simply updates the products table with the new amount of stock using the current stock & taking away the amount bought. NEW is a special keyword that holds the new values inserted. If you were running a trigger on update or delete you would be able to use the OLD keyword which would hold the data before the deletion or update. To stop it from updating every row we limit it using a WHERE. In this case only update where the product ids match.

One very important note about triggers. The syntax requires you use semi-colons (;) inside your trigger. Sometimes this can cause problems as the semi-colon is normally the default delimiter. If you notice any strange problems like commands being cut off try changing your delimiter temporarily like this:

Hopefully this small, but helpful (I hope) look at MySQL’s powerful trigger system has made you think about looking into their use a littler further. I know I’ve decided to take a much deeper look at their use for my coding.

DatabaseDBDelimiterMySQLSQLTrigger