Showing posts with label mysql-trigger. Show all posts
Showing posts with label mysql-trigger. Show all posts

Wednesday, October 25, 2023

Mysql trigger for checking duplicate record in Table before insert using trigger

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.

Assume we do not have any unique index added hence we have to manage uniqueness by programatically.
The insert trigger executes prior to the insertion process. Only if the code succeeds, will the insert take place. To prevent the row from being inserted, an error must be generated.

Below is the trigger
DROP TRIGGER if EXISTS before_user_insert;

DELIMITER $$

CREATE TRIGGER before_user_insert BEFORE INSERT ON users FOR EACH ROW
  BEGIN
    DECLARE v1 BIGINT(20);
    DECLARE m1 VARCHAR(400);
    SELECT id INTO v1 FROM users WHERE id<>NEW.id AND `mobile`=NEW.`mobile`;
    IF (v1 IS NOT NULL) THEN
      SELECT CONCAT('Duplicate mobile number at record - ', v1) INTO m1;
      SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = m1;
    END IF;
  END$$

DELIMITER ;
Which will output as below:

SQLSTATE[45000]: <>: 1644 Duplicate mobile number at record - 43