Mysql trigger for checking duplicate record in Table before insert

Question

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

My User table structure is:

USERID | FirstName | LastName | EmailID | Mobile

I am using the below code to create 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 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 mistake.

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

@KP. the table was used for a long time, now I am changing it. It has many duplicate Mobile numbers present which can not be removed so i cant apply Unique constraint. Is trigger good approach? — Sandeep

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-05-10T10:56:53

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);

Mysql trigger for checking duplicate record in Table before insert

1 Answers