current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I created a table with some data in it, and a trigger like this:

DROP TRIGGER IF EXISTS verifica_cpf;
DELIMITER //
CREATE TRIGGER verifica_cpf
    BEFORE INSERT ON `usuario` FOR EACH ROW
    BEGIN
        IF (SELECT COUNT(*) FROM usuario WHERE  cpf = new.cpf) > 1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CPF EXISTENTE!';            
        END IF;
    END//
DELIMITER ;

Basically it checks for duplicate 'cpf' values before inserts.

Let's say that in my table, there's one entry with this cpf value: 873.255.218-12

If i try to insert another entry with the same cpf value, the trigger won't work. But if i insert it again, it will.

How can i solve this?

share|improve this question
1  
Change > with =. –  Mihai Jun 25 at 1:57
add comment

1 Answer

up vote 0 down vote accepted

You can change this by changing the 1 to 0:

DROP TRIGGER IF EXISTS verifica_cpf;
DELIMITER //
CREATE TRIGGER verifica_cpf
    BEFORE INSERT ON `usuario` FOR EACH ROW
    BEGIN
        IF (SELECT COUNT(*) FROM usuario WHERE  cpf = new.cpf) > 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CPF EXISTENTE!';            
        END IF;
    END//
DELIMITER ;

Or by using the more efficient if exists:

DROP TRIGGER IF EXISTS verifica_cpf;
DELIMITER //
CREATE TRIGGER verifica_cpf
    BEFORE INSERT ON `usuario` FOR EACH ROW
    BEGIN
        IF exists (SELECT 1 FROM usuario WHERE  cpf = new.cpf) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CPF EXISTENTE!';            
        END IF;
    END//
DELIMITER ;
share|improve this answer
    
Can't believe i didn't notice the '>'....Thanks! –  Joao Victor Jun 25 at 12:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.