inicio mail me! sindicaci;ón

MySQL BEFORE INSERT trigger as check constraint

Oktober 21, 2009 at 11:03 am · Filed under Allgemein

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.

Schreibe einen Kommentar