Email validation through MySQL triggers and signals

Did some research and found out that MySQL provides triggers and signals. Triggers allow you to run some code when an certain event occurs, and Signals allow you to raise exceptions in your code.

Here is a SQL script to show you how it is done:

CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `test`;

CREATE TABLE IF NOT EXISTS `test`.`entity_email` (

    `emailID` INT NOT NULL,

    `email` VARCHAR(64) NOT NULL,

    PRIMARY KEY (`emailID`) )

ENGINE = InnoDB

COMMENT = "Generic Email table";

DELIMITER $$

USE `test`$$

CREATE TRIGGER `trg_entity_email_insert` BEFORE INSERT ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

CREATE TRIGGER `trg_entity_email_update` BEFORE UPDATE ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

DELIMITER ;

Now MySQL will perform e-mail validation EVERY time you insert or edit the record. If it doesn't match, it will fail (MySQL outputs an "Unhandled user-defined exception condition" as the error message).

Hope this helps!