ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


MySQL Triggers Tryout

by Peter Gulutzan
02/03/2005

MySQL 5.0, the alpha version of MySQL that's available for testing new features, has trigger support. This is no surprise, as triggers were promised in the MySQL Development Roadmap, but it's a novel experience to work with one of the big "MySQL can't do that" features and watch MySQL doing it.

For these tests I downloaded the most recent MySQL 5.0 source as described in the MySQL Reference Manual section Installing from the Development Source Tree. Material downloaded from the source tree is generally much newer--and less tested--than what you find on the MySQL 5.0 Downloads page.

Test-Driving Triggers

I start the mysql client program from a Linux shell, and with my first statement I make sure that I have version 5:

mysql> SELECT version();
+-------------------+
| version()         |
+-------------------+
| 5.0.2-alpha-debug |
+-------------------+
1 row in set (0.00 sec)

Then I create a table in a test database, create a trigger, and run an INSERT statement to test the trigger.

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.27 sec)
mysql> USE test_db;
Database changed
mysql> CREATE TABLE t (column1 TINYINT);
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE TRIGGER t_bi              /* line 1 */
    -> BEFORE INSERT ON t               /* line 2 */
    -> FOR EACH ROW                     /* line 3 */
    -> SET @x = @x + 1;                 /* line 4 */
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x = 0;                      /* line 5 */
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (1),(NULL); /* line 6 */
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT @x;                       /* line 7 */
+------+
| @x   |
+------+
| 2    |
+------+
1 row in set (0.01 sec)

To begin with the conclusion: the above exercise proves that triggers work with MySQL. To demonstrate why, I'll have to go through the CREATE TRIGGER statement one line at a time.

Explaining Triggers

CREATE TRIGGER trigger_name            /* line 1 */

Naturally, the first part is CREATE TRIGGER and the name of the new trigger. I tend to use a convention: I start with the name of the table, then an underscore, then one of these six codes: bi, ai, bu, au, bd, or ad. Those codes stand for, respectively:

BEFORE INSERT ON table_name            /* line 2 */
or AFTER INSERT ON table_name
or BEFORE UPDATE ON table_name
or AFTER UPDATE ON table_name
or BEFORE DELETE ON table_name
or AFTER DELETE ON table_name

Those are the six possible times that a trigger might be activated. A trigger is always associated with a data-change statement on a single base table. My trigger, which has the clause BEFORE INSERT ON t, will be activated when I do INSERTs on table t.

FOR EACH ROW                           /* line 3 */

Specifically, the activation will happen for each row that I insert. If I INSERT zero rows, which is possible with INSERT ... SELECT statements, then zero activations take place. If I INSERT 1,000 rows, then 1,000 activations take place. Standard SQL allows you to say FOR EACH STATEMENT instead, which would mean that the activation happens once, no matter how many rows there are.

SET @x = @x + 1;                       /* line 4 */

Finally, there is the "body" of the trigger. When a trigger is activated, the statement in the trigger's body is executed. In my trigger the statement is SET @x = @x + 1, which increments the variable @x each time activation happens.

In other words, @x is a counter. Whenever an INSERT for a row happens, @x goes up. Of course, if @x starts with a NULL value then nothing will happen, and that's why I start by initializing the counter:

SET @x = 0;                            /* line 5 */

The big test moment comes when I INSERT:

INSERT INTO t VALUES (1),(NULL);       /* line 6 */

Every time I insert a row in t, the value of @x should rise, because there is a FOR EACH ROW trigger on t that says that's what should happen. Thus when I SELECT

SELECT @x;                              /* line 7 */

the result will be 2, because there were two rows.

Fancier Triggers

Now I want to make a fancy UPDATE trigger, but first I have to say that the delimiter, the end-of-statement marker for the mysql client, will be //. I must do this because my trigger will contain ; within the trigger body.

mysql> DELIMITER //

Here is the fancy UPDATE trigger:

mysql> CREATE TRIGGER t_bu
    -> BEFORE UPDATE ON t
    -> FOR EACH ROW
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR 1264 SET new.column1 = -1;
    ->   SET new.column1 = new.column1 * 2;
    ->   END;//
Query OK, 0 rows affected (0.00 sec)

Trigger t_bu will be activated for each row that I update, and its body is a compound statement. The syntax for compound statements for stored procedures is already all in the MySQL Reference Manual, so I only need to give an English translation here: multiply the "new" value of column1 by 2, but if that causes an out-of-range error, then set the "new" value of column1 to -1.

The body of a trigger can contain pretty well anything that the body of a MySQL function can contain; in addition, it can contain references to the "new" or "old" values of a row.

What could possibly cause an error? Well, column1's data type is TINYINT, so its maximum value is 127, and MySQL 5.0 can do type checking in the right circumstances. I'll do an UPDATE, which causes the "Out of range value" situation.

mysql> UPDATE t SET column1 = column1 + 100;//
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 1

For the first row in table t, the column1 value is 1, so I add 100 to it, making 101, and then the trigger doubles it, making 202--but that activates the exception handler, which sets it back to -1. For the second row, the column1 value is NULL, so nothing happens. I can prove that happened by looking at what's in table t now:

mysql> SELECT * FROM t//
+---------+
| column1 |
+---------+
|      -1 |
|    NULL |
+---------+
2 rows in set (0.00 sec)

Conclusion and Current Caveats

MySQL Users Conference 2005.

Thus it's clear that triggers work for both INSERT and UPDATE, that trigger bodies can contain complex statements, and that BEFORE triggers can read and change the values that I'm inserting or updating. All of this is excellent news. Therefore, I will end with some grim reminders to prevent the outbreak of excess enthusiasm.

REMINDER: MySQL alpha versions are unstable. Search for the keyword trigger* in the online MySQL bug database, and be cautious accordingly.

REMINDER: MySQL functions have severe limitations. For example, they can't SELECT from a table. Trigger activations are like function calls and are subject to the same limitations.

Peter Gulutzan works for MySQL AB as senior software architect. He lives in Edmonton, Alberta.


Return to ONLamp.com.

Copyright © 2009 O'Reilly Media, Inc.