Description:
Sometimes the trigger can be confusing. For example:
1. Trigger can be created successfully to handle an non-existing tables(t2), and it's definer can be an no-existing user(user_1), just like:
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`user_1`@`%`*/ /*!50003 TRIGGER `trigger_1` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN
INSERT INTO t2 VALUES(new.id);
END */;;
As a result, when update on t1, errors will happen. In my opinion, this trigger should not be successfully created in this case. Errors should be found in the create phase instead of the use phase.
2. Trigger can be created successfully to handle an non-existing column(col_1), just like:
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`user`@`%`*/ /*!50003 TRIGGER `trigger_2` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN
select col_1 from t3;
END */;;
If t1 has the field col_1, but t3 does not, and user don't know or forget trigger2, then the error report will be quite confusing. The error message is "Unknown column 'col_1' in 'field list'", but it is insuffient, because we don't know "col_1" is in which table. As a user, I will first check table "t1" instead of trigger "trigger_2"(table "t3").
This might be by design, but in some cases, it can be really confusing.
How to repeat:
As mentioned above.
Suggested fix:
Checks about trigger should be done in the create phase instead of the use phase.
Description: Sometimes the trigger can be confusing. For example: 1. Trigger can be created successfully to handle an non-existing tables(t2), and it's definer can be an no-existing user(user_1), just like: DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`user_1`@`%`*/ /*!50003 TRIGGER `trigger_1` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN INSERT INTO t2 VALUES(new.id); END */;; As a result, when update on t1, errors will happen. In my opinion, this trigger should not be successfully created in this case. Errors should be found in the create phase instead of the use phase. 2. Trigger can be created successfully to handle an non-existing column(col_1), just like: DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`user`@`%`*/ /*!50003 TRIGGER `trigger_2` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN select col_1 from t3; END */;; If t1 has the field col_1, but t3 does not, and user don't know or forget trigger2, then the error report will be quite confusing. The error message is "Unknown column 'col_1' in 'field list'", but it is insuffient, because we don't know "col_1" is in which table. As a user, I will first check table "t1" instead of trigger "trigger_2"(table "t3"). This might be by design, but in some cases, it can be really confusing. How to repeat: As mentioned above. Suggested fix: Checks about trigger should be done in the create phase instead of the use phase.