MySQL BEFORE INSERT trigger as check constraint
Since MySQL does neither have real check constraints nor a way to raise an exception in a stored procedure, we found it not instantly obvious, how we could *reject* a certain row on insert, based on a certain condition.
A nice way we found was to set the value in question to NULL, based on the condition and let the NOT NULL constraint do its work.
ALTER TABLE sessions MODIFY session_id varchar(255) NOT NULL;
DROP TRIGGER IF EXISTS check_sessionid;
DELIMITER $$
CREATE TRIGGER check_sessionid BEFORE INSERT ON sessions
FOR EACH ROW BEGIN
IF NOT NEW.session_id REGEXP '^[[:xdigit:]]{32}$' THEN
SET NEW.session_id = NULL;
END IF;
END;
$$
DELIMITER ;
The trigger will let any 32 character string with only HEX characters for the column session_id pass and rejects the rest.
> INSERT INTO sessions (session_id) VALUES ('ffffffffffffffffffffffffffffffff');
Query OK, 1 row affected (0.01 sec)
> INSERT INTO sessions (session_id) VALUES ('fffffffffffffffffffffffffffffffg');
ERROR 1048 (23000): Column 'session_id' cannot be null
Happy triggering.
