MySQL Triggers Tryoutby Peter Gulutzan
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.
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
TRIGGER statement one line at a time.
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:
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
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
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
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 INTO t VALUES (1),(NULL); /* line 6 */
Every time I insert a row in
t, the value of
should rise, because there is a
FOR EACH ROW trigger on
t that says that's what should happen. Thus when I
SELECT @x; /* line 7 */
the result will be
2, because there were two rows.
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
client, will be
//. I must do this because my trigger will contain
; within the trigger body.
mysql> DELIMITER //
Here is the fancy
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)
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
2, but if that causes an out-of-range error, then set
the "new" value of
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
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
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
the second row, the
column1 value is
NULL, so nothing happens. I
can prove that happened by looking at what's in table
mysql> SELECT * FROM t// +---------+ | column1 | +---------+ | -1 | | NULL | +---------+ 2 rows in set (0.00 sec)
Conclusion and Current Caveats
Thus it's clear that triggers work for both
UPDATE, that trigger bodies can contain complex statements, and
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.