In this post we’ll review how MySQL triggers can affect queries.
Contrary to what the documentation states, we can activate triggers even while operating on views:
https://dev.mysql.com/doc/refman/5.7/en/triggers.html
Important: MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL server.
Be on the lookout if you use and depend on triggers, since it’s not the case for updatable views! We have reported a documentation bug for this but figured it wouldn’t hurt to mention this as a short blog post, too. 🙂 The link to the bug in question is here:
https://bugs.mysql.com/bug.php?id=86575
Now, we’ll go through the steps we took to test this, and their outputs. These are for the latest MySQL version (5.7.18), but the same results were seen in 5.5.54, 5.6.35, and MariaDB 10.2.5.
First, we create the schema, tables and view needed:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | mysql> CREATE SCHEMA view_test; Query OK, 1 row affected (0.00 sec) mysql> USE view_test; Database changed mysql> CREATE TABLE `main_table` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `letters` varchar(64) DEFAULT NULL, -> `numbers` int(11) NOT NULL, -> `time` time NOT NULL, -> PRIMARY KEY (`id`), -> INDEX col_b (`letters`), -> INDEX cols_c_d (`numbers`,`letters`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.31 sec) mysql> CREATE TABLE `table_trigger_control` ( -> `id` int(11), -> `description` varchar(255) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.25 sec) mysql> CREATE VIEW view_main_table AS SELECT * FROM main_table; Query OK, 0 rows affected (0.02 sec) |
Indexes are not really needed to prove the point, but were initially added to the tests for completeness. They make no difference in the results.
Then, we create the triggers for all possible combinations of [BEFORE|AFTER] and [INSERT|UPDATE|DELETE]. We will use the control table to have the triggers insert rows, so we can check if they were actually called by our queries.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> CREATE TRIGGER trigger_before_insert BEFORE INSERT ON main_table FOR EACH ROW -> INSERT INTO table_trigger_control VALUES (NEW.id, "BEFORE INSERT"); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TRIGGER trigger_after_insert AFTER INSERT ON main_table FOR EACH ROW -> INSERT INTO table_trigger_control VALUES (NEW.id, "AFTER INSERT"); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TRIGGER trigger_before_update BEFORE UPDATE ON main_table FOR EACH ROW -> INSERT INTO table_trigger_control VALUES (NEW.id, "BEFORE UPDATE"); Query OK, 0 rows affected (0.19 sec) mysql> CREATE TRIGGER trigger_after_update AFTER UPDATE ON main_table FOR EACH ROW -> INSERT INTO table_trigger_control VALUES (NEW.id, "AFTER UPDATE"); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TRIGGER trigger_before_delete BEFORE DELETE ON main_table FOR EACH ROW -> INSERT INTO table_trigger_control VALUES (OLD.id, "BEFORE DELETE"); Query OK, 0 rows affected (0.18 sec) mysql> CREATE TRIGGER trigger_after_delete AFTER DELETE ON main_table FOR EACH ROW -> INSERT INTO table_trigger_control VALUES (OLD.id, "AFTER DELETE"); Query OK, 0 rows affected (0.05 sec) |
As you can see, they will insert the ID of the row in question, and the combination of time/action appropriate for each one. Next, we will proceed in the following manner:
- INSERT three rows in the main table
- UPDATE the second
- DELETE the third
The reasoning behind doing it against the base table is to check that the triggers are working correctly, and doing what we expect them to do.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> INSERT INTO main_table VALUES (1, 'A', 10, time(NOW())); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO main_table VALUES (2, 'B', 20, time(NOW())); Query OK, 1 row affected (0.14 sec) mysql> INSERT INTO main_table VALUES (3, 'C', 30, time(NOW())); Query OK, 1 row affected (0.17 sec) mysql> UPDATE main_table SET letters = 'MOD' WHERE id = 2; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> DELETE FROM main_table WHERE id = 3; Query OK, 1 row affected (0.10 sec) |
And we check our results:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | mysql> SELECT * FROM main_table; +----+---------+---------+----------+ | id | letters | numbers | time | +----+---------+---------+----------+ | 1 | A | 10 | 15:19:14 | | 2 | MOD | 20 | 15:19:14 | +----+---------+---------+----------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM table_trigger_control; +------+---------------+ | id | description | +------+---------------+ | 1 | BEFORE INSERT | | 1 | AFTER INSERT | | 2 | BEFORE INSERT | | 2 | AFTER INSERT | | 3 | BEFORE INSERT | | 3 | AFTER INSERT | | 2 | BEFORE UPDATE | | 2 | AFTER UPDATE | | 3 | BEFORE DELETE | | 3 | AFTER DELETE | +------+---------------+ 10 rows in set (0.00 sec) |
Everything is working as it should, so let’s move on with the tests that we really care about. We will again take the three steps mentioned above, but this time directly on the view.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> INSERT INTO view_main_table VALUES (4, 'VIEW_D', 40, time(NOW())); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO view_main_table VALUES (5, 'VIEW_E', 50, time(NOW())); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO view_main_table VALUES (6, 'VIEW_F', 60, time(NOW())); Query OK, 1 row affected (0.11 sec) mysql> UPDATE view_main_table SET letters = 'VIEW_MOD' WHERE id = 5; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> DELETE FROM view_main_table WHERE id = 6; Query OK, 1 row affected (0.01 sec) |
And we check our tables:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | mysql> SELECT * FROM main_table; +----+----------+---------+----------+ | id | letters | numbers | time | +----+----------+---------+----------+ | 1 | A | 10 | 15:19:14 | | 2 | MOD | 20 | 15:19:14 | | 4 | VIEW_D | 40 | 15:19:34 | | 5 | VIEW_MOD | 50 | 15:19:34 | +----+----------+---------+----------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM view_main_table; +----+----------+---------+----------+ | id | letters | numbers | time | +----+----------+---------+----------+ | 1 | A | 10 | 15:19:14 | | 2 | MOD | 20 | 15:19:14 | | 4 | VIEW_D | 40 | 15:19:34 | | 5 | VIEW_MOD | 50 | 15:19:34 | +----+----------+---------+----------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM table_trigger_control; +------+---------------+ | id | description | +------+---------------+ | 1 | BEFORE INSERT | | 1 | AFTER INSERT | | 2 | BEFORE INSERT | | 2 | AFTER INSERT | | 3 | BEFORE INSERT | | 3 | AFTER INSERT | | 2 | BEFORE UPDATE | | 2 | AFTER UPDATE | | 3 | BEFORE DELETE | | 3 | AFTER DELETE | | 4 | BEFORE INSERT | | 4 | AFTER INSERT | | 5 | BEFORE INSERT | | 5 | AFTER INSERT | | 6 | BEFORE INSERT | | 6 | AFTER INSERT | | 5 | BEFORE UPDATE | | 5 | AFTER UPDATE | | 6 | BEFORE DELETE | | 6 | AFTER DELETE | +------+---------------+ 20 rows in set (0.00 sec) |
As seen in the results, all triggers were executed, even when the queries were run against the view. Since this was an updatable view, it worked. On the contrary, if we try on a non-updatable view it fails (we can force ALGORITHM = TEMPTABLE to test it).
1 2 3 4 5 6 7 8 9 | mysql> CREATE ALGORITHM=TEMPTABLE VIEW view_main_table_temp AS SELECT * FROM main_table; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO view_main_table_temp VALUES (7, 'VIEW_H', 70, time(NOW())); ERROR 1471 (HY000): The target table view_main_table_temp of the INSERT is not insertable-into mysql> UPDATE view_main_table_temp SET letters = 'VIEW_MOD' WHERE id = 5; ERROR 1288 (HY000): The target table view_main_table_temp of the UPDATE is not updatable mysql> DELETE FROM view_main_table_temp WHERE id = 5; ERROR 1288 (HY000): The target table view_main_table_temp of the DELETE is not updatable |
As mentioned before, MariaDB shows the same behavior. The difference, however, is that the documentation is correct in mentioning the limitations, since it only shows the following:
https://mariadb.com/kb/en/mariadb/trigger-limitations/
Triggers cannot operate on any tables in the mysql, information_schema or performance_schema database.
Corollary to the Discussion
It’s always good to thoroughly check the documentation, but it’s also necessary to test things and prove the documentation is showing the real case (bugs can be found everywhere, not just in the code :)).
Very good cache. Much appreciated one 🙂