How do i write a trigger in mysql to check for duplicate records before inserting

Question

users(id,company,name)

This is my schema for the table. How should i write a trigger to check for the duplicates before inserting the new row. I need to check the incoming data is not duplicated with the incoming record. if the incoming record is existing that record should not be inserted to the table. If its not duplicating that record hould be inserted!

Below is the trigger i tried but it gives an error at the 'last end' line

DELIMITER $$
CREATE TRIGGER userDuplicate 
BEFORE INSERT ON `users` FOR EACH ROW

BEGIN
    DECLARE recordId VARCHAR(999);

    SET recordId = (SELECT id FROM users WHERE company= NEW.companyAND name = NEW.name);

    IF recordId IS NOT NULL
    THEN
        INSERT INTO users (company,name) VALUES (NEW.name,NEW.company);
    END IF;
END;
$$ DELIMITER;

Have you tried creating a unique index? https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql — Surberus, Sep 02 '16 at 03:59
you need trigger or just to insert the non-duplicate records without error? — Arun Krish, Sep 02 '16 at 04:15
i need a trigger to insert any no of rows which are not duplicating and not throwing any exceptions or errors — Nisal Malinda Livera, Sep 02 '16 at 04:35

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

According to the accepted answer here: How to abort INSERT operation in MySql trigger? you can not have a MySQL trigger cancel the INSERT. It's easy on PostgreSQL (BEFORE INSERT trigger simply returns NULL), but MySQL can not handle that.

The thing you can do, though, is an INSERT IGNORE:

INSERT IGNORE INTO users (company, name) 
VALUES ('ABC','DEF');

edited May 23 '17 at 11:51

Community

1
1

answered Sep 02 '16 at 09:34

Boris Schegolev

3,601
5
21
34

How do i write a trigger in mysql to check for duplicate records before inserting

1 Answers1