how to use a trigger to validate input data code example

Example 1: how to use a trigger to validate input data

DELIMITER $$

CREATE TRIGGER example_before_insert_allow_only_one_active
     BEFORE INSERT ON example_tbl FOR EACH ROW
     BEGIN
          IF NEW.active = 1 AND (SELECT COUNT(id) FROM example_tbl 
               WHERE active=1 AND foreign_key_id=NEW.foreign_key_id) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
          END IF;
     END;
$$

CREATE TRIGGER example_before_update_allow_only_one_active
     BEFORE UPDATE ON example_tbl  FOR EACH ROW
     BEGIN
          IF NEW.active = 1 AND (SELECT COUNT(id) FROM example_tbl
               WHERE id<>NEW.id AND active=1 AND foreign_key_id=NEW.foreign_key_id) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
          END IF;
     END;
$$

Example 2: how to use a trigger to validate input data

-- validate email
CREATE TRIGGER `person_validate_insert`
	BEFORE INSERT
	ON `person`
	FOR EACH ROW
BEGIN
	IF NEW.`email` NOT LIKE '%_@%_.__%' THEN
		SIGNAL SQLSTATE VALUE '45000'
			SET MESSAGE_TEXT = '[table:person] - `email` column is not valid';
	END IF;
END;