Sandeep Sandeep - 6 months ago 30
MySQL Question

Mysql trigger for checking duplicate record in Table before insert

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?

Answer

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