XJDBBShq

1CREATE TABLE `users` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `login` varchar(45) NOT NULL,
4  `email` varchar(255) NOT NULL,
5  `md5_Email` char(32) NOT NULL,
6  PRIMARY KEY (`id`)
7)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
8	
9DELIMITER // 
10CREATE TRIGGER checkExistEmails BEFORE INSERT ON users
11FOR EACH ROW 
12BEGIN
13IF NOT EXISTS (SELECT 1 FROM users WHERE md5_Email = MD5(TRIM(new.email))) THEN
14 SET NEW.md5_Email = MD5(TRIM(NEW.email));
15ELSE
16 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email Already Exists';
17END IF;
18END //
19DELIMITER ;
20	
21INSERT IGNORE INTO users (`login`,`email`,`md5_Email`)
22VALUES ('Login18','ericsanchez@yahoo.com','XXXX'),('Login19','christopher76@hotmail.com','XXXX'),('Login20','ericsanchez@yahoo.com','XXXX'),('Login21','lisa86@hotmail.com','XXXX')
23	
24ERROR 1644 (45000): Email Already Exists
25MariaDB [DATABASE]> select * from users;
26+----+---------+---------------------------+----------------------------------+
27| id | login   | email                     | md5_Email                        |
28+----+---------+---------------------------+----------------------------------+
29|  1 | Login18 | ericsanchez@yahoo.com     | 2eebca4622b871fed0efbd9a8e89802f |
30|  2 | Login19 | christopher76@hotmail.com | 3777f449598bcf28f98a9e1711633ced |
31+----+---------+---------------------------+----------------------------------+