Shulou(Shulou.com)06/01 Report--

Recently, I saw that August í n, an engineer at Percona, wrote a blog about MySQL triggers and updatable views. For more links, please see https://www.percona.com/blog/2017/06/14/triggers-and-updatable-views/.

The basic description of triggers in the official documentation is as follows:

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.

MySQL triggers are triggered only by SQL statements at the table level, views are not allowed, and table-level operations at the API level are not triggered.

There seems to be nothing wrong with this description, after all, triggers do exist on specific tables, and it sounds reasonable to be triggered by tables. But Agust í n thought that the description of the official document was not rigorous enough, and voluntarily submitted a bug to the official, of course, he said so, on the one hand, he had a deep understanding of the content in this area, and on the other hand, he did a lot of tests, covering MySQL 5.5,5.6,5.7.18 (the latest version at present), so on a case-by-case basis, this is a very professional and rigorous attitude.

The steps for the Agust í n test are as follows:

He created a test table main_table, an information record table table_trigger_control, and a view view_main_table.

Of course, I couldn't help it. I also tested it myself. Of course, I made adjustments on the basis of him and simplified the testing process appropriately.

We create two tables, one is the base table, one is the record table, and the other is the view.

Base 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`)

) ENGINE=InnoDB

Control table

CREATE TABLE `table_trigger_ control` (

`id`int (11)

`accountion` varchar (255)

) ENGINE=InnoDB; View

CREATE VIEW view_main_table AS SELECT * FROM main_table

Then create three triggers corresponding to the insert,update,delete operation

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_after_update after update on main_table for each row

Insert into table_trigger_control values (new.id,'AFTER UPDATE')

Create trigger trigger_after_delete after delete on main_table for each row

Insert into table_trigger_control values (old.id,'AFTER DELETE')

The test scenario is relatively simple, that is, you can test several DML scenarios, such as:

1) insert 3 rows of data

2) Line 2 of update

3) Line 3 of delete

The specific statements are as follows:

INSERT INTO main_table VALUES (1, 'Aids, 10, time (NOW ()

INSERT INTO main_table VALUES (2, 'Bamboo, 20, time (NOW ()

INSERT INTO main_table VALUES (3, 'Clearing, 30, time (NOW ()

UPDATE main_table SET letters = 'MOD' WHERE id = 2

DELETE FROM main_table WHERE id = 3; after the test, let's look at the final result:

Select * from main_table

+-- +

| | id | letters | numbers | time | |

+-- +

| | 1 | A | 10 | 23:03:09 |

| | 2 | MOD | 20 | 23:03:13 |

+-+ and the information triggered by the trigger is recorded in table_trigger_control.

> SELECT * FROM table_trigger_control

+-+ +

| | id | description |

+-+ +

| | 1 | AFTER INSERT |

| | 2 | AFTER INSERT |

| | 3 | AFTER INSERT |

| | 2 | AFTER UPDATE |

| | 3 | AFTER DELETE |

+-+ +

5 rows in set (0.00 sec) 3 Insert,1 update,1 delete, which happens to be 5.

There doesn't seem to be anything special here, and let's take a look at the view, which is also a key to testing here.

The specific statements are as follows:

INSERT INTO view_main_table VALUES (4, 'VIEW_D', 40, time (NOW ()

INSERT INTO view_main_table VALUES (5, 'VIEW_E', 50, time (NOW ()

INSERT INTO view_main_table VALUES (6, 'VIEW_F', 60, time (NOW ()

UPDATE view_main_table SET letters = 'VIEW_MOD' WHERE id = 5

DELETE FROM view_main_table WHERE id = 6

The result of running the statement is as follows:

[test] > select * from main_table

+-- +

| | id | letters | numbers | time | |

+-- +

| | 1 | A | 10 | 23:03:09 |

| | 2 | MOD | 20 | 23:03:13 |

| | 4 | VIEW_D | 40 | 23:04:43 |

| | 5 | VIEW_MOD | 50 | 23:04:46 |

+-- +

4 rows in set (0.00 sec) and the contents of the information record table after the trigger is triggered are as follows:

> SELECT * FROM table_trigger_control

+-+ +

| | id | description |

+-+ +

| | 1 | AFTER INSERT |

| | 2 | AFTER INSERT |

| | 3 | AFTER INSERT |

| | 2 | AFTER UPDATE |

| | 3 | AFTER DELETE |

| | 4 | AFTER INSERT |

| | 5 | AFTER INSERT |

| | 6 | AFTER INSERT |

| | 5 | AFTER UPDATE |

| | 6 | AFTER DELETE |

+-+ +

From this point of view, 10 rows in set (0.00 sec) was also triggered successfully for 5 times.

So it looks like the watch has the same effect.

Let's change the pose and create a new view:

> CREATE ALGORITHM=TEMPTABLE VIEW view_main_table_temp AS SELECT * FROM main_table

Then continue to insert a record, and the result is wrong.

> 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 is not sure whether the before insert trigger has been triggered at this time, you can add another trigger.

CREATE TRIGGER trigger_before_insert BEFORE INSERT ON main_table FOR EACH ROW

INSERT INTO table_trigger_control VALUES (NEW.id, "BEFORE INSERT"); keep trying or fail.

> 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 looks at the trigger control information table and finds that there are no new records, indicating that this type of view will not be triggered successfully.

> select * from table_trigger_control

+-+ +

| | id | description |

+-+ +

| | 1 | AFTER INSERT |

| | 2 | AFTER INSERT |

| | 3 | AFTER INSERT |

| | 2 | AFTER UPDATE |

| | 3 | AFTER DELETE |

| | 4 | AFTER INSERT |

| | 5 | AFTER INSERT |

| | 6 | AFTER INSERT |

| | 5 | AFTER UPDATE |

| | 6 | AFTER DELETE |

+-+ +

10 rows in set (0.00 sec), of course, not only insert,update and delete have the same effect.

UPDATE view_main_table_temp SET letters = 'VIEW_MOD' WHERE id = 5

DELETE FROM view_main_table_temp WHERE id = 5; in this respect, MariaDB í n is particularly mentioned, and the documentation of Agust in this respect is commendable.

Https://mariadb.com/kb/en/mariadb/trigger-limitations/

Of course, the official attitude is also worthy of recognition. The bug has been confirmed soon and will be updated immediately.

Therefore, there are many ways to share for the community, and curiosity about technology is the eternal driving force for learning and progress.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.