The SQL Standard also defines the syntax and semantics for triggers that are to be fired once for an entire statement, regardless of the number of rows that is touched by that statement. Unsurprisingly, these are called statement-level triggers.
MySQL does not support statement-level triggers. However, there's a quick hack to emulate
BEFORE STATEMENT triggers.Using the ROW_COUNT function
The hack depends on the
ROW_COUNT() function. This function returns the number of rows updated, inserted, or deleted by the preceding statement. An example update statement for the sakila database might illustrate that:
UPDATE sakila.category
SET name = UCASE(name)
;
Query OK, 16 rows affected (0.22 sec)
Rows matched: 16 Changed: 16 Warnings: 0
SELECT row_count();
+-------------+
| row_count() |
+-------------+
| 16 |
+-------------+
1 row in set (0.04 sec)
(BTW, you might want to backup your sakila installation and restore it or find some other way to undo the changes performed by the
UPDATE statement.)So, the update statement updated all 16 rows in the category table.
Using ROW_COUNT inside TRIGGERs
I wondered how
ROW_COUNT() would react if it would be called from within a conventional MySQL row-level trigger, so I decided to test it.First, we'll set up a table for which we want to define the triggers:
CREATE TABLE test_row_count(
id int
);
To log trigger activity for operations occurring on this table, we'll need another table:
CREATE TABLE test_row_count_log(
id int AUTO_INCREMENT PRIMARY KEY
, test_row_count_id int
, trigger_event enum('DELETE','INSERT','UPDATE')
, trigger_time enum('BEFORE','AFTER')
, row_count int
);
Now we can create triggers for each of the three DML events insert, update and delete, and for each of these events, we can write both a before and a after tigger. So, potentially, we could write up to 3 * 2 = 6 different triggers. Let's start out with the two
INSERT triggers and see what happens:
delimiter //
CREATE TRIGGER bir_test_row_count
BEFORE INSERT ON test_row_count
FOR EACH ROW
INSERT
INTO test_row_count_log(
test_row_count_id
, trigger_event
, trigger_time
, row_count
) VALUES (
new.id
, 'INSERT'
, 'BEFORE'
, row_count()
);
//
CREATE TRIGGER air_test_row_count
AFTER INSERT ON test_row_count
FOR EACH ROW
INSERT
INTO test_row_count_log(
test_row_count_id
, trigger_event
, trigger_time
, row_count
) VALUES (
new.id
, 'INSERT'
, 'AFTER'
, row_count()
);
//
delimiter ;
Now let's see what happens when we insert a row into the
test_row_count table.:
INSERT
INTO test_row_count(
id
) VALUES (
1
)
;
Executing this
INSERT will fire both the triggers exactly once, so we expect to see two rows inside the test_row_count_log table. We expect to see one row in
test_row_count_log corresponding to the instant immediately before a row was created in test_row_count; we also expect one row corresponding to the moment immediately after the insert on test_row_count occurred. We're most interested in the value stored in the row_count column of the test_row_count_log table, as this would've captured the value returned by the ROW_COUNT() function. So:
SELECT *
FROM test_row_count_log
ORDER BY id
;
Here's what happened:
+----+-------------------+---------------+--------------+-----------+
| id | test_row_count_id | trigger_event | trigger_time | row_count |
+----+-------------------+---------------+--------------+-----------+
| 1 | 1 | INSERT | BEFORE | -1 |
| 2 | 1 | INSERT | AFTER | 1 |
+----+-------------------+---------------+--------------+-----------+
2 rows in set (0.08 sec)
So, both triggers fired once, and both inserted a row in the
test_row_count_log. Actually, it's quite funny seeing the -1 value in the row_count column of the row corresponding to the firing of the BEFORE trigger. I mean, being a count, I'd expect the value 0 rather than -1. For the row corresponding to the
AFTER trigger, the value in the row_count value makes more sense. At this point, we can certainly agree that indeed exactly one row is inserted, and so the value is 1 in this case.When we execute a statement that inserts multiple rows, we can observe another interesting phenomenon:
INSERT
INTO test_row_count
VALUES (2),(3)
;
SELECT *
FROM test_row_count_log
WHERE test_row_count_id in (2,3)
ORDER BY id
;
+----+-------------------+---------------+--------------+-----------+
| id | test_row_count_id | trigger_event | trigger_time | row_count |
+----+-------------------+---------------+--------------+-----------+
| 3 | 2 | INSERT | BEFORE | -1 |
| 4 | 2 | INSERT | AFTER | 1 |
| 5 | 3 | INSERT | BEFORE | 1 |
| 6 | 3 | INSERT | AFTER | 1 |
+----+-------------------+---------------+--------------+-----------+
4 rows in set (0.02 sec)
Even though the statement fires the BEFORE trigger multiple times, the
-1 is returned only for the first row that is touched by the statement. For all subsequent rows, ROW_COUNT() returns a 1 for de row_count column. Emulating the statement level trigger
This behaviour offers the opportunity to emulate a statement-level trigger. Let's add a
level column to the test_row_count_log table and rewrite the BEFORE trigger to demonstrate this:
ALTER TABLE test_row_count_log
ADD level enum('ROW','STATEMENT')
DEFAULT 'ROW'
;
DROP TRIGGER bir_test_row_count
;
delimiter //
CREATE TRIGGER bir_test_row_count
BEFORE INSERT ON test_row_count
FOR EACH ROW
BEGIN
DECLARE v_row_count int DEFAULT row_count();
IF v_row_count!=1 THEN
INSERT
INTO test_row_count_log(
test_row_count_id
, trigger_event
, trigger_time
, row_count
, level
) VALUES (
new.id
, 'INSERT'
, 'BEFORE'
, v_row_count
, 'STATEMENT'
);
END IF;
INSERT
INTO test_row_count_log(
test_row_count_id
, trigger_event
, trigger_time
, row_count
, level
) VALUES (
new.id
, 'INSERT'
, 'BEFORE'
, v_row_count
, 'ROW'
);
END;
//
delimiter ;
INSERT
INTO test_row_count (id)
SELECT id + 3
FROM test_row_count
;
SELECT *
FROM test_row_count_log
WHERE test_row_count_id > 3
ORDER BY id
;
+----+-------------------+---------------+--------------+-----------+-----------+
| id | test_row_count_id | trigger_event | trigger_time | row_count | level |
+----+-------------------+---------------+--------------+-----------+-----------+
| 7 | 4 | INSERT | BEFORE | -1 | STATEMENT |
| 8 | 4 | INSERT | BEFORE | -1 | ROW |
| 9 | 4 | INSERT | AFTER | 1 | ROW |
| 10 | 5 | INSERT | BEFORE | 1 | ROW |
| 11 | 5 | INSERT | AFTER | 1 | ROW |
| 12 | 6 | INSERT | BEFORE | 1 | ROW |
| 13 | 6 | INSERT | AFTER | 1 | ROW |
+----+-------------------+---------------+--------------+-----------+-----------+
Using an
IF statement to test the value returned by the ROW_COUNT() value, we can detect if the trigger is handling the first row, and if it is, we can do some special work. In this case, we simply insert a row in the test_row_count_log table, marking it by storing the value 'STATEMENT' in the level column.Some closing notes
The
IF statement tests for those cases where the ROW_COUNT() function returns something else than 1, rather than checking explicitly for equality with -1. This is because I suspect that the value of -1 might be a bug, and it wouldn't surprise me if that would be changed to be a 0 in a newer version of the server. There's another observation that might convince you to be careful with this hack. At least in MySQL versions 5.0.18 and 5.1.7 (the platform I tested),
ROW_COUNT() behaves differently when you wrap a procedure around the code that's now inside the triggers. I found that ROW_COUNT() always returns 0 in this case. As far as I can see now, at least three distinct contexts are involved: procedures, triggers and immediate statements. There might be even more of course, but my observations with ROW_COUNT() imply at least these three.If you plan to use
ROW_COUNT() to emulate a statement-level BEFORE trigger, it's probably a good idea to be careful when upgrading, and convince yourself that the code still works in the newer server version. I also tested this hack for
UPDATE and DELETE statements - that worked for me. Of course, if anyone has any new insights, or methods to achieve a statement-level trigger in MySQL, I'd be very interested. Just leave a comment here and tell the world about it. One of the things that keep intriguing me if it would be possible to emulate an
AFTER statement-level trigger. As far as I can see now, this is impossible, as there is now information regarding the total number of rows that is handled by the statement that fires the triggers. Of course, If you know a hack that does this, I'd be most grateful to hear about it.
