There is a table users. You need to write a trigger that checks if there is the same login and password in the table. If so, you need to display an error message. But the trigger is not created, the message that it is impossible to execute the trigger takes off. Please help because I am completely new.

CREATE TABLE `users` ( `id_User` int(11) NOT NULL, `login` varchar(45) COLLATE utf8_bin NOT NULL, `email` varchar(45) COLLATE utf8_bin NOT NULL, `password` varchar(45) COLLATE utf8_bin NOT NULL, `status` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; CREATE TRIGGER `before_insert_user` BEFORE INSERT ON `users` DECLARE some_var int(11) default 0; FOR EACH ROW SELECT COUNT(*) INTO some_var FROM users WHERE users.email=NEW.email AND users.login=NEW.login IF (some_var > 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error for inserting login or email'; END IF; END

Errors were found during the processing of your request: This request was not executed: "CREATE TRIGGER before_insert_user BEFORE INSERT ON users FOR EACH email = NEW.email AND users.login = NEW.login IF (some_var> 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error for inserting login or email'; END IF; END "

MySQL response:

  • 2
    declare to for each row, missing begin. Take a closer look at the examples of dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html. Moreover, you do not need to write a trigger to check the uniqueness, it is enough to create a unique index from these two fields. PS although the check for uniqueness with the password looks very strange. obtained with different passwords can be multiple users with the same name. This behavior will cause a lot of security problems. - Mike
  • @Mike, I think there will be something like "You can not register with this password - it is already used by the user vasya". But I may be wrong. - Alexander Belinsky

0