CODEWITHSUNDEEP
mysqltriggers
Arpan Kc
Arpan Kc

Reputation: 938

Sql Trigger condition check not working

I am creating a checking condition for a field in my data base. In the table called student, I have a field for Gender. This field should accept one of two values 'M' or 'F'. I have created a trigger like so:

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `student`
FOR EACH ROW
BEGIN
    IF NEW.Gender<>'M' OR NEW.Gender<>'F' THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT = 'should be M or F';
    END IF;
END$$   
DELIMITER ;

The trigger is created but the field 'Gender' won't accept any values including M or F.

Upvotes: 0

Views: 555

Answers (1)

juergen d
juergen d

Reputation: 204746

Your condition is wrong. It is always true. use

IF NEW.Gender not in ('M', 'F') THEN

or use

IF NEW.Gender<>'M' AND NEW.Gender<>'F' THEN

Upvotes: 1

Related Questions