Bug #89093 | Trigger order, not works as I expect | ||
---|---|---|---|
Submitted: | 2 Jan 2018 13:08 | Modified: | 9 Feb 2018 4:37 |
Reporter: | tapin13 none | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.7.20 | OS: | Ubuntu (14.04) |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
Tags: | order, trigger |
[2 Jan 2018 13:08]
tapin13 none
[9 Jan 2018 4:27]
MySQL Verification Team
mysql [localhost] {msandbox} (test) > CREATE TABLE `test` ( -> `aaa` int(11) DEFAULT NULL, -> `step1` int(11) DEFAULT NULL, -> `step2` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 -> -> ; Query OK, 0 rows affected (0.01 sec) mysql [localhost] {msandbox} (test) > delimiter $$ mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > CREATE -> /*!50017 DEFINER = 'root'@'localhost' */ -> TRIGGER `master_test_a` BEFORE INSERT ON `test` -> FOR EACH ROW #PRECEDES `master_test_b` -> -> BEGIN -> -> SET NEW.`step1` = NEW.`aaa` + 1; -> -> END; -> $$ Query OK, 0 rows affected (0.01 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > CREATE -> /*!50017 DEFINER = 'root'@'localhost' */ -> TRIGGER `master_test_b` BEFORE INSERT ON `test` -> FOR EACH ROW FOLLOWS `master_test_a` -> -> BEGIN -> -> SET NEW.`step2` = NEW.`step1` + 1; -> -> END; -> $$ Query OK, 0 rows affected (0.01 sec) mysql [localhost] {msandbox} (test) > delimiter ; mysql [localhost] {msandbox} (test) > insert into test (aaa) values (1); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (2); Query OK, 1 row affected (0.04 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (3); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (4); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (5); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > select * from test; +------+-------+-------+ | aaa | step1 | step2 | +------+-------+-------+ | 1 | 2 | 3 | | 2 | 3 | 4 | | 3 | 4 | 5 | | 4 | 5 | 6 | | 5 | 6 | 7 | +------+-------+-------+ 5 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (10); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (9); Query OK, 1 row affected (0.02 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (8); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (7); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > select * from test; +------+-------+-------+ | aaa | step1 | step2 | +------+-------+-------+ | 1 | 2 | 3 | | 2 | 3 | 4 | | 3 | 4 | 5 | | 4 | 5 | 6 | | 5 | 6 | 7 | | 10 | 11 | 12 | | 9 | 10 | 11 | | 8 | 9 | 10 | | 7 | 8 | 9 | +------+-------+-------+ 9 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) >
[9 Jan 2018 4:28]
MySQL Verification Team
mysql [localhost] {msandbox} (test) > delete from test; Query OK, 9 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > alter table test add primary key (aaa); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > insert into test (aaa) values (1); Query OK, 1 row affected (0.01 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (2); Query OK, 1 row affected (0.03 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (3); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (4); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (10); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (9); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (8); Query OK, 1 row affected (0.01 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (5); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > select * from test; +-----+-------+-------+ | aaa | step1 | step2 | +-----+-------+-------+ | 1 | 2 | 3 | | 2 | 3 | 4 | | 3 | 4 | 5 | | 4 | 5 | 6 | | 5 | 6 | 7 | | 8 | 9 | 10 | | 9 | 10 | 11 | | 10 | 11 | 12 | +-----+-------+-------+ 8 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) >
[9 Jan 2018 4:35]
MySQL Verification Team
mysql [localhost] {msandbox} (test) > drop table test; Query OK, 0 rows affected (0.09 sec) mysql [localhost] {msandbox} (test) > CREATE TABLE `test` ( -> `aaa` int(11) DEFAULT NULL, -> `step1` int(11) DEFAULT NULL, -> `step2` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 -> ; Query OK, 0 rows affected (0.01 sec) mysql [localhost] {msandbox} (test) > delimiter $$ mysql [localhost] {msandbox} (test) > CREATE -> /*!50017 DEFINER = 'root'@'localhost' */ -> TRIGGER `master_test_b` BEFORE INSERT ON `test` -> FOR EACH ROW #FOLLOWS `master_test_a` -> -> BEGIN -> -> SET NEW.`step2` = NEW.`step1` + 1; -> -> END; -> $$ Query OK, 0 rows affected (0.01 sec) mysql [localhost] {msandbox} (test) > CREATE -> /*!50017 DEFINER = 'root'@'localhost' */ -> TRIGGER `master_test_a` BEFORE INSERT ON `test` -> FOR EACH ROW PRECEDES `master_test_b` -> -> BEGIN -> -> SET NEW.`step1` = NEW.`aaa` + 1; -> -> END; -> $$ Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > show triggers; -> ; -> $$ +---------------+--------+-------+------------------------------------------------+--------+------------------------+------------- ------------------------------------------------------------------------------------------------------------------------------+--- -------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | De finer | character_set_client | collation_connection | Database Collation | +---------------+--------+-------+------------------------------------------------+--------+------------------------+------------- ------------------------------------------------------------------------------------------------------------------------------+--- -------------+----------------------+----------------------+--------------------+ | master_test_a | INSERT | test | BEGIN SET NEW.`step1` = NEW.`aaa` + 1; END | BEFORE | 2018-01-09 05:31:47.98 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_B Y_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ ci | | master_test_b | INSERT | test | BEGIN SET NEW.`step2` = NEW.`step1` + 1; END | BEFORE | 2018-01-09 05:31:43.98 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci | +---------------+--------+-------+------------------------------------------------+--------+------------------------+------------- ------------------------------------------------------------------------------------------------------------------------------+--- -------------+----------------------+----------------------+--------------------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > delimiter ; mysql [localhost] {msandbox} (test) > insert into test (aaa) values (1); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (2); Query OK, 1 row affected (0.05 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (10); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (9); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (3); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into test (aaa) values (5); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > select * from test; +------+-------+-------+ | aaa | step1 | step2 | +------+-------+-------+ | 1 | 2 | 3 | | 2 | 3 | 4 | | 10 | 11 | 12 | | 9 | 10 | 11 | | 3 | 4 | 5 | | 5 | 6 | 7 | +------+-------+-------+ 6 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) >
[9 Jan 2018 4:37]
MySQL Verification Team
Hi, I don't see a bug here please elaborate? thanks Bogdan
[10 Feb 2018 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".