MySQL Forums
Forum List  »  Triggers

Insufficient checks for trigger
Posted by: Huaxiong Song
Date: January 02, 2023 10:57PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Insufficient checks for trigger
167
January 02, 2023 10:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.