CODEWITHSUNDEEP
mysqltriggers
Ed Heal
Ed Heal

Reputation: 60037

MySql tables and triggers

My understanding is that MySql ignores CHECK in the CREATE TABLE syntax. I was therefore thinking - why not use triggers?

For example I have a table of people and one field is a phone number. Is it possible to construct a trigger to validate that the phone number is valid (e.g. the area code) and on the event of an invalid phone number to make the insertion fail with an appropriate error message?

Upvotes: 0

Views: 115

Answers (2)

Devart
Devart

Reputation: 122042

Yes, you can create a trigger to validate inserted values and call an unknown procedure. For example -

CREATE TABLE table1
  id INT(11) NOT NULL,
  phone VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (id)
);

DELIMITER $$

CREATE trigger1
BEFORE INSERT
ON table1
FOR EACH ROW
BEGIN
  IF NOT NEW.phone REGEXP '^[0-9]{3}-[0-9]{2}-[0-9]{2}$' THEN
    CALL error; -- Call nonexistent procedure
  END IF;
END
$$

DELIMITER ;

Upvotes: 1

Mchl
Mchl

Reputation: 62395

Yes it is possible and often used solution.

Since MySQL 5.5 you can use SIGNAL to raise error message. Before that, you need to invoke error in some other way. I usually insert error message into table with UNIQUE index, so that this insertion fails, and my error message is contained within MySQL's error message.

Upvotes: 1

Related Questions