CODEWITHSUNDEEP
mysqltriggers
Grimat
Grimat

Reputation: 49

MySQL Trigger "syntax error, unexpected END, expecting ';'"

I'm trying to create a trigger to validate an email address in a customer table, but am getting the error

syntax error, unexpected END, expecting ';'

My code is as follows:

CREATE TRIGGER insert_validation_email
 BEFORE INSERT ON customer FOR EACH ROW
    BEGIN
        IF (NEW.CustomerEmail NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$')
        THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot add or update row: invalid email address'
    END* IF
END

END* is where it is underlined with the error (the asterisk is not there)

There are other validation triggers which will be included, so the begin and end statement is needed

Upvotes: 0

Views: 1244

Answers (1)

Grimat
Grimat

Reputation: 49

Fixed by changing delimiter and putting ; after if block and final END. Thanks Gordon Linoff and a_horse_with_no_name for helping

DELIMITER $$
CREATE TRIGGER before_insert_email
     BEFORE INSERT ON customer FOR EACH ROW
        BEGIN
            IF (NEW.CustomerEmail NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$')
            THEN
                SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Cannot add or update row: invalid email address';
        END IF;
    END$$
DELIMITER ;

Upvotes: 0

Related Questions