Bug #86575 Documentation: queries on VIEWs can activate TRIGGERs
Submitted: 3 Jun 2017 18:38 Modified: 15 Jun 2017 23:01
Reporter: Agustín G Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.5 5.6 5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: views triggers

[3 Jun 2017 18:38] Agustín G
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.
[5 Jun 2017 8:49] MySQL Verification Team
Hello Agustín,

Thank you for the report.

Thanks,
Umesh
[15 Jun 2017 14:19] Paul DuBois
Posted by developer:
 
Agustin, you are correct. Will change the docs to say:

MySQL triggers activate only for changes made to tables by SQL
statements. This includes changes to base tables that underlie
updatable views.
[15 Jun 2017 23:01] Agustín G
Sounds good, Paul.

Thanks!
Agustín.