MySQL Trigger – How To, Example, Tutorial, and Syntax

A MySQL trigger defines a course of action for MySQL to take when data is changed within a table. These changes can be inserts, updates, or deletes made to a table. Support for triggers was added to MySQL 5.0.2. An example of a trigger would be updating inventory each time a product is purchased.

In this example I’ll use a MySQL trigger to compute a numerical hash of a varchar field everytime the varchar is updated or when a new row is inserted. Our example table will have a simple auto-incrementing primary key, the products_model column as a varchar (because models can contain dashes and even letters in this case), and model_hash which will be a numerical representation of the products model using a hashing function.

Why am I doing this? I’m using the hash because lookups and groupings are performed faster on an integer than on a varchar. Using this approach I was able to chop a 14 second query down to 1 second. In fact the application in question wouldn’t even load on our test server prior to me optimizing the code (there were other factors in play, but this about half of the performance penalties).

Normally in this instance I would do something like this in the code rather than at the database level, but there are times when that is not feasible. For instance, at my work the code is very old and not OOP. There is a not a single method that controls updates to the products table. Its very likely that more than a handful of scripts can update the product table at any time. Thus in this instance a database trigger is needed to get the project done fast. It’s not ideal, but it will work.

Create an Example Table

Now that we have our table lets fill it with some data.

MySQL Trigger Syntax for BEFORE UPDATE

Here is the working syntax which took me quite a bit of googling and then finally asking to figure out:

MySQL Trigger Syntax for BEFORE INSERT

Breaking down the syntax we are telling MySQL to first create a new trigger called updateModelHash. Next we say for each row that is being changed we want you to set the value of model_hash. Using the NEW keyword we tell it what the new value is, too read an older value we would have said OLD.model_hash. Lastly the CONV(), RIGHT(), and MD5() are simply used to create a unique numerical hash (credit goes to the authors of High Performance MySQL for giving me the hashing idea).

Some errors you can receive when doing this include “ERROR 1442 (HY000): Cant update table ‘tbl’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger” as a result of bad syntax such as this:

MySQL will prevent this trigger from executing because it will detect an infinite loop. Because after an update, triggering an update, would of course trigger an after update trigger…

Here are some commands to drop and show triggers:

Drop me a comment if this helped your or if you feel I missed something, thanks for reading.

MySQL Documentation:
Using Triggers
Trigger Syntax

Other Resources:
http://mark.koli.ch/2009/07/mysql-trigger-error-1442-hy000-cant-update-table-tbl-in-stored-functiontrigger-because-it-is-al.html
http://crazytoon.com/2008/03/03/mysql-error-1442-hy000-cant-update-table-t1-in-stored-functiontrigger-because-it-is-already-used-by-statement-which-invoked-this-stored-functiontrigger/


11 Comments

  • Jason Stryker says:

    Dude.. I Do Have a Problem… when i open a website… it says You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND ct_language=’pt” at line 1 How Do I Fix That?

  • anonymous says:

    Was this on an osc cart ? What other cool mods did you do ?

    • chris says:

      Yes it was. Too many “cool” mods. Glad I never have to work in OS Commerce again. At the time I had done a lot of modifications. Implemented an entire flat file cache system to reduce database lookups, automatic image sprites, integrated vBulletin posts into our pages for user generated content, also integrated OSc authentication w/ vBulletin and vice-versa.

      Tons of other mods too like some WP integration. Osc blows though, half my tweets are anger directed at the devs of that flying spaghetti monster.

  • thejas says:

    Thanks dude.
    It was so easy to learn

  • prahalad says:

    We need to change mysql delimiter when using triggers as said in the following reference :

    http://questionfriends.com/questions/5046388e8048a/mysql-trigger-delete-multiple-tables

  • Nice tutorial…..!!!

  • Yang says:

    Hi dude,
    It’s a nice tutorial, but you didn’t explain how to fix the infinite loop problem. You said use drop and show trigger commands. I don’t quite understand what’s the purpose of show trigger commands. My understanding is you drop the trigger after you update the hash value and then recreate it? Could you show us the full solution? thanks

    • chris says:

      Those additional commands were just informational. Not much to do with the tutorial. I created this solution for a project ages ago with a company I no longer work for so I can’t post any other information. Sorry.

  • Vadim says:

    Thank you. Very nice and clean tutorial. Really helped with triggers. However, it would be even better, if would have an example of listening to events of an individual column.

  • Ivan says:

    Huh, I got that loop error trying to do basically the same thing and I gave up and went a different way. I’ll try it tomorrow.

    Thx

  • boa says:

    can a trigger be apply to a database not only a table??