W3qpgLrQ

1CREATE TABLE `users` (
2  `uid` int(11) NOT NULL AUTO_INCREMENT,
3  `user_name` varchar(20) DEFAULT NULL,
4  `email` varchar(255) NOT NULL,
5  `md5_email` char(32) NOT NULL,
6  `entered` date DEFAULT NULL,
7  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
8  `country` varchar(5) NOT NULL DEFAULT 'DE',
9  PRIMARY KEY (`uid`),
10  KEY `md5_eml` (`md5_email`),
11  KEY `entered` (`entered`)
12) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
13/*!50100 PARTITION BY RANGE (uid)
14(PARTITION id50k VALUES LESS THAN (50000) ENGINE = MyISAM,
15 PARTITION id100k VALUES LESS THAN (100000) ENGINE = MyISAM,
16 PARTITION id150k VALUES LESS THAN (150000) ENGINE = MyISAM,
17 PARTITION id200k VALUES LESS THAN (200000) ENGINE = MyISAM,
18 PARTITION idOthers VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 
19     
20DELIMITER $$ 
21CREATE TRIGGER checkExistEmail BEFORE INSERT ON users 
22FOR EACH ROW 
23BEGIN
24IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
25 SET NEW.md5_email = MD5(TRIM(NEW.email));
26ELSE
27 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email Already Exists';
28END IF;
29END $$
30DELIMITER ; 
31     
32DECLARE _dups INT DEFAULT FALSE;
33FOR EACH ROW 
34  BEGIN
35  IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
36    SET NEW.md5_email = MD5(TRIM(NEW.email));
37  ELSE
38    SET _dups = TRUE;
39  END IF;
40  IF _dups THEN
41    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Some Emails Already Exists';
42  END IF;
43END $$