insert multi on a table handled by trigger I have this table

CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `md5_email` char(32) NOT NULL,
  `entered` date DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `country` varchar(5) NOT NULL DEFAULT 'DE',
  PRIMARY KEY (`uid`),
  KEY `md5_eml` (`md5_email`),
  KEY `entered` (`entered`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (uid)
(PARTITION id50k VALUES LESS THAN (50000) ENGINE = MyISAM,
 PARTITION id100k VALUES LESS THAN (100000) ENGINE = MyISAM,
 PARTITION id150k VALUES LESS THAN (150000) ENGINE = MyISAM,
 PARTITION id200k VALUES LESS THAN (200000) ENGINE = MyISAM,
 PARTITION idOthers VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */


&amp; this trigger to check duplicate emails

DELIMITER $$ 
CREATE TRIGGER checkExistEmail BEFORE INSERT ON users 
FOR EACH ROW 
BEGIN
IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
 SET NEW.md5_email = MD5(TRIM(NEW.email));
ELSE
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email Already Exists';
END IF;
END $$
DELIMITER ;


the trigger works fine with single insert cmd

but with multi insert it stops when the first duplicate email found

how can i make it ignore duplicates without aborting all operation ?


  mysql  Ver 15.1 Distrib 10.1.21-MariaDB
You have a contradiction -- On the one hand, you want to "ignore" dups.  On the other hand, you want to "SIGNAL" about dups.  Which is it?

If you want to signal after trying to insert multiple rows, you won't know which row(s) were dups.  If that is OK, then consider something like (I'm not sure of syntax):

DECLARE _dups INT DEFAULT FALSE;
FOR EACH ROW 
  BEGIN
  IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
    SET NEW.md5_email = MD5(TRIM(NEW.email));
  ELSE
    SET _dups = TRUE;
  END IF;
  IF _dups THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Some Emails Already Exists';
  END IF;
END $$


Alternatively, you could probably use rows_affected() to see how many were successfully inserted.