Agfdhyk

I have a list of records that I have to insert, but I do not want duplicates to be inserted with the same fields, I need to verify if this record exists and do not insert it and continue to analyze the following

BEGIN

IF NOT EXISTS (SELECT * FROM Leads WHERE n_documento = new.n_documento AND programa = new.programa) THEN

    INSERT INTO Leads

    VALUES

    (NULL, new.nombres, new.apellidos, new.n_documento, new.fecha_de_nacimiento, new.telefono, new.email, new.ciudad, 

    new.pais, new.anos_de_experiencia, new.origen, new.informacion_adicional, new.fecha, new.programa, new.estado, new.fecha_charla,

    new.grado_de_estudio, new.empresa, new.cargo, null);

END IF;

my trigger does not work, it does not validate and it allows entering the duplicates

Handling this in a trigger is not the best or most efficient place to handle this.

What you could do is build a compound unique index. Which would then in turn NOT allow you to insert a second row where the selected column values match identically to another row.

eg.

ALTER TABLE `YourSchema`.`YourTableName` 

ADD UNIQUE INDEX `CompoundUniqueIndexName` (`Col1` ASC, `Col2` ASC, `Col3` ASC);

Now running the following one time:

INSERT INTO `YourSchema`.`YourTableName` (`Col1`, `Col2`, `Col3`) VALUES ('Bob', 'Jim', 'Bill');

Will successfully add a row. But you run it a second time, and you will get the following error and the row will not be insert:

Error Code: 1062. Duplicate entry 'Bob-Jim-Bill' for key
'CompoundUniqueIndexName'

Caveat

I have found Every value in your compound index needs to have a value. So it is important to set each column involved in your compound index to be NOT NULL. Additionally it may be in your interest to also supply a default value like '0' or something if you can not control the input to assure a value is present.

The reason for this is I'm not entirely sure of, But I know the following two statements will NOT produce a 'Duplicate Entry' error, because Col3 will be NULL without the above precautions.

INSERT INTO `YourSchema`.`YourTableName` (`Col1`, `Col2`) VALUES ('Bob', 'Jim');

Then again,

INSERT INTO `YourSchema`.`YourTableName` (`Col1`, `Col2`) VALUES ('Bob', 'Jim');

This type of condition may be more performance if you use it in queries.

SELECT TOP 1 1 FROM Leads