Mysql trigger for checking duplicate record in Table before insert

I am to creating a trigger for my table User which checks for duplicates (Mobile number) in the User table before inserting a new row.

My User table structure is:

USERID | FirstName | LastName | EmailID | Mobile

I am using the below code to create a trigger:

DELIMITER $$
CREATE TRIGGER Before_Insert_User
BEFORE INSERT ON User
FOR EACH ROW
BEGIN
  IF (NOT EXISTS(SELECT * FROM User WHERE Mobile = NEW.Mobile)) THEN
    INSERT INTO User (USERID, FirstName, LastName, EmailID, Mobile,) 
    VALUES (NEW.USERID, NEW.FirstName, NEW.LastName, NEW.EmailID, NEW.Mobile); 
  END IF;
END$$
DELIMITER ;

But this trigger is giving me an error as below while inserting new records:

Cannot update the user information (Can't update table 'User' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.)

I am not getting where I am doing a mistake.

How can I write a trigger for checking if the value being inserted is already present in User table?

Solution

You are thinking about this backwards. The trigger runs before the insert. If the code succeeds, then the insert goes ahead. You need to generate an error if you don't want the row inserted:

DELIMITER $$

CREATE TRIGGER Before_Insert_User
BEFORE INSERT ON User
FOR EACH ROW
BEGIN
  IF (EXISTS(SELECT 1 FROM User WHERE Mobile = NEW.Mobile)) THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'INSERT failed due to duplicate mobile number';
  END IF;
END$$
DELIMITER ;

However, this is the wrong way to implement this constraint. You just want Mobile to be unique in the table, so use a unique constraint or index:

alter table user add constraint unq_user_mobile unique(mobile);