Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is not stable yet, so users still need to use workaround.
Here it is. Create 2 procedures as following:
DROP PROCEDURE IF EXISTS raise_application_error;
DROP PROCEDURE IF EXISTS get_last_custom_error;
DROP TABLE IF EXISTS RAISE_ERROR;
DELIMITER $$
CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTIC
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL);
SELECT CODE, MESSAGE INTO @error_code, @error_message;
INSERT INTO RAISE_ERROR VALUES(NULL);
END;
$$
CREATE PROCEDURE get_last_custom_error() SQL SECURITY INVOKER DETERMINISTIC
BEGIN
SELECT @error_code, @error_message;
END;
$$
DELIMITER ;
CALL raise_application_error(1234, 'Custom message');
CALL get_last_custom_error();
Example: table which stores only odd numbers.
DROP TABLE IF EXISTS ex1;
DROP TRIGGER IF EXISTS ex1_bi;
DROP TRIGGER IF EXISTS ex1_bu;
CREATE TABLE ex1(only_odd_numbers INT UNSIGNED);
DELIMITER $$
CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROW
BEGIN
IF NEW.only_odd_numbers%2 != 0 THEN
CALL raise_application_error(3001, 'Not odd number!');
END IF;
END
$$
CREATE TRIGGER ex1_bu BEFORE UPDATE ON ex1 FOR EACH ROW
BEGIN
IF NEW.only_odd_numbers%2 != 0 THEN
CALL raise_application_error(3001, 'Not odd number!');
END IF;
END
$$
DELIMITER ;
Usage:
mysql> INSERT INTO ex1 VALUES(2);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO ex1 VALUES(3);
ERROR 1048 (23000): Column 'F1' cannot be null
mysql> CALL get_last_custom_error();
+-------------+-----------------+
| @error_code | @error_message |
+-------------+-----------------+
| 3001 | Not odd number! |
+-------------+-----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM ex1;
+------------------+
| only_odd_numbers |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
