Mysql trigger for checking duplicate record in Table before insert

Question

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?

@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
– Sandeep, Commented May 10, 2016 at 10:53

Sandeep · Accepted Answer · 2016-05-10 14:08:17Z

10

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

edited May 10, 2016 at 14:08

Sandeep

1,5287 gold badges22 silver badges32 bronze badges

answered May 10, 2016 at 10:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sandeep Over a year ago

Ya, i had the same idea of making mobile unique, but there are many duplicates already present and we can't remove the so I thought of implementing triggers. BTW the above code is giving error as

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''45000' SET MESSAGE_TEXT = 'INSERT failed due to duplicate mobile number';   END' at line 6

Paul Man Over a year ago

What if you want the trigger to quietly ignore the insert or update if there is a duplicate and not generate any errors?

Gordon Linoff Over a year ago

@PaulMan . . . Questions should be asked as questions, not comments.

Hitesh Over a year ago

@PaulMan You should use stored procedures to prevent the insertion, because once insert query is executed, then the only way to prevent the insertion is SQL errors/signals.

Collectives™ on Stack Overflow

Mysql trigger for checking duplicate record in Table before insert

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related