Description:
Regarding the following documentation page:
https://dev.mysql.com/doc/refman/5.7/en/triggers.html
It is mentioned in the notes:
"""
Important
MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, [...]
"""
This is not entirely true, according to what I get in the tests shown below. If the VIEW is updatable (in this case, it will use the MERGE algorithm), then running a query against the VIEW will activate the TRIGGERs from the underlying table.
The steps were tried in these versions (all of them exhibit the same behaviour):
5.5.54
5.6.35
5.7.18
How to repeat:
CREATE SCHEMA view_test;
USE view_test;
DROP TABLE IF EXISTS main_table;
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;
DROP TABLE IF EXISTS `table_trigger_control`;
CREATE TABLE `table_trigger_control` (
`id` int(11),
`description` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- We have the tables created, now let's create the triggers
-- The second table is just to log events to check if triggers were used
CREATE TRIGGER trigger_before_insert BEFORE INSERT ON main_table FOR EACH ROW
INSERT INTO table_trigger_control VALUES (NEW.id, "BEFORE INSERT");
CREATE TRIGGER trigger_after_insert AFTER INSERT ON main_table FOR EACH ROW
INSERT INTO table_trigger_control VALUES (NEW.id, "AFTER INSERT");
CREATE TRIGGER trigger_before_update BEFORE UPDATE ON main_table FOR EACH ROW
INSERT INTO table_trigger_control VALUES (NEW.id, "BEFORE UPDATE");
CREATE TRIGGER trigger_after_update AFTER UPDATE ON main_table FOR EACH ROW
INSERT INTO table_trigger_control VALUES (NEW.id, "AFTER UPDATE");
CREATE TRIGGER trigger_before_delete BEFORE DELETE ON main_table FOR EACH ROW
INSERT INTO table_trigger_control VALUES (OLD.id, "BEFORE DELETE");
CREATE TRIGGER trigger_after_delete AFTER DELETE ON main_table FOR EACH ROW
INSERT INTO table_trigger_control VALUES (OLD.id, "AFTER DELETE");
-- We have created a trigger for every case
INSERT INTO main_table VALUES (1, 'A', 10, time(NOW()));
INSERT INTO main_table VALUES (2, 'B', 20, time(NOW()));
INSERT INTO main_table VALUES (3, 'C', 30, time(NOW()));
--
INSERT INTO main_table VALUES (5, 'E', 50, time(NOW()));
INSERT INTO main_table VALUES (6, 'F', 60, time(NOW()));
-- We inserted data to test. First three rows will be used directly via the table
-- The second three rows will be used via the view (we will insert ID 4 later)
UPDATE main_table SET letters = 'MOD' WHERE id = 2;
DELETE FROM main_table WHERE id = 3;
SELECT * FROM main_table;
SELECT * FROM table_trigger_control;
-- Now we create the view:
DROP VIEW IF EXISTS view_main_table;
CREATE VIEW view_main_table AS SELECT * FROM main_table;
SELECT * FROM view_main_table;
-- The sleep is just so that times differ a bit, there is really no need for this, though
SELECT sleep(2);
-- We operate on the view, and then check the control table for changes
INSERT INTO view_main_table VALUES (4, 'D', 40, time(NOW()));
UPDATE view_main_table SET letters = 'VIEW_MOD' WHERE id = 5;
DELETE FROM view_main_table WHERE id = 6;
SELECT * FROM main_table;
SELECT * FROM view_main_table;
SELECT * FROM table_trigger_control;
Suggested fix:
This looks like a documentation bug, so fixing the note in the provided link (and analogous ones for other versions) will suffice. If you see this as an implementation bug, though, let me know.
If needed, I can attach a file with test results for each version, but I believe you should be able to reproduce this easily with the exact steps provided.
Thanks!
Agustín.