12 11 2010
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
1 2 3 4 5
CREATE TABLE `products` ( `products_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `products_model` VARCHAR( 30 ) NOT NULL , `model_hash` BIGINT( 20 ) UNSIGNED NOT NULL ) ENGINE = MYISAM ;
Now that we have our table lets fill it with some data.
1 2 3 4
INSERT INTO `products` (`products_id`, `products_model`, `model_hash`) VALUES (322,'251322-3', 4256645115), (323, '414239-3', 2543588158), (324, '265998-0', 2393726240);
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:
CREATE TRIGGER updateModelHash BEFORE UPDATE ON products FOR EACH ROW SET NEW.model_hash = CONV(RIGHT(MD5(NEW.products_model), 16), 16, 10);
MySQL Trigger Syntax for BEFORE INSERT
CREATE TRIGGER insertModelHash BEFORE INSERT ON products FOR EACH ROW SET NEW.model_hash = CONV(RIGHT(MD5(NEW.products_model), 16), 16, 10);
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:
1 2 3 4 5 6 7
delimiter $$ CREATE TRIGGER pHash AFTER UPDATE ON products FOR EACH ROW BEGIN UPDATE products SET model_hash = CONV(RIGHT(MD5(products_model), 16), 16, 10); END$$ delimiter ;
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 TRIGGER nameOfTrigger; SHOW TRIGGERS;
Drop me a comment if this helped your or if you feel I missed something, thanks for reading.