Using Stored Routines, Triggers, and Scheduled Events - MySQL - page 333

• Preprocess values and modify them, if you won't want to reject them outright. For
example, map out-of-range values to be in range or sanitize values to put them in
canonical form, if you permit entry of close variants.
Suppose that you have a table of contact information such as name, state of residence,
email address, and website URL:
CREATE TABLE contact_info
(
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR ( 30 ), # state of residence
state CHAR ( 2 ), # state of residence
email VARCHAR ( 50 ), # email address
url VARCHAR ( 255 ), # web address
PRIMARY KEY ( id )
);
For entry of new rows, you want to enforce constraints or perform preprocessing as
follows:
• State of residence values are two-letter US state codes, valid only if present in the
states table. (In this case, you could declare the column as an ENUM with 50 mem‐
bers, so it's more likely you'd use this lookup-table technique with columns for
which the set of valid values is arbitrarily large or changes over time.)
• Email address values must contain an @ character to be valid.
• For website URLs, strip any leading http:// to save space.
To handle these requirements, create a BEFORE INSERT trigger:
CREATE TRIGGER bi_contact_info BEFORE INSERT ON contact_info
FOR EACH ROW
BEGIN
IF ( SELECT COUNT ( * ) FROM states WHERE abbrev = NEW . state ) = 0 THEN
SIGNAL SQLSTATE 'HY000'
SET MYSQL_ERRNO = 1525 , MESSAGE_TEXT = 'invalid state code' ;
END IF ;
IF INSTR ( NEW . email , '@' ) = 0 THEN
SIGNAL SQLSTATE 'HY000'
SET MYSQL_ERRNO = 1525 , MESSAGE_TEXT = 'invalid email address' ;
END IF ;
SET NEW . url = TRIM ( LEADING 'http://' FROM NEW . url );
END ;
To also handle updates, define a BEFORE UPDATE trigger with the same body as bi_con
tact_info .