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

SIGNAL can also raise warning conditions, not just errors. The following routine,
drop_user_warn() , is similar to the drop_user() routine shown earlier, but instead of
printing a message for nonexistent users, it generates a warning that can be displayed
with SHOW WARNINGS . SQLSTATE value 01000 and error 1642 indicate a user-defined
unhandled exception, which the routine signals along with an appropriate message:
CREATE PROCEDURE drop_user_warn ( user TEXT , host TEXT )
BEGIN
DECLARE account TEXT ;
DECLARE CONTINUE HANDLER FOR 1396
BEGIN
DECLARE msg TEXT ;
SET msg = CONCAT ( 'Unknown user: ' , account );
SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1642 , MESSAGE_TEXT = msg ;
END ;
SET account = CONCAT ( QUOTE ( user ), '@' , QUOTE ( host ));
CALL exec_stmt ( CONCAT ( 'DROP USER ' , account ));
END ;
Give it a test:
mysql> CALL drop_user_warn('bad-user','localhost');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------+
| Warning | 1642 | Unknown user: 'bad-user'@'localhost' |
+---------+------+--------------------------------------+
9.11. Using Triggers to Preprocess or Reject Data
Problem
There are conditions you want to check for data entered into a table, but you don't want
to write the validation logic for every INSERT .
Solution
Centralize the input-testing logic into a BEFORE INSERT trigger.
Discussion
You can use triggers to perform several types of input checks:
• Reject bad data by raising a signal. This gives you access to stored program logic
for more latitude in checking values than is possible with static constraints such as
NOT NULL .