As of version 5.5, MySQL still cannot raise errors. Why that's so and whether it's a scandal can be discussed, but no-one will disagree it's a problem. What's the workaround?
The basic solution is to force an artefactual error and set a custom error message:
SET @sql = F1('SELECT 1 FROM `%s`', 'Good morning!');
PREPARE stmt FROM @sql;
DROP PREPARE stmt;
This tells the user "Table 'Good morning!' does not exist." Here is a smoother approach:
CREATE SCHEMA IF NOT EXISTS sys;
USE sys;
DROP PROCEDURE IF EXISTS raiseapperror;
DROP PROCEDURE IF EXISTS getlasterror;
DROP TABLE IF EXISTS errormsgs;
DELIMITER go
CREATE PROCEDURE raiseapperror(IN code INT, IN msg VARCHAR(255))
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS errormsgs(f1 INT NOT NULL);
SELECT code, msg INTO @error_code, @error_message;
INSERT INTO errormsgs VALUES(NULL);
END;
go
CREATE PROCEDURE getlasterror() SQL SECURITY INVOKER DETERMINISTIC
SELECT @error_code, @error_message;
go
DELIMITER ;
-- Example:
USE test;
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 go
CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROW
BEGIN
IF NEW.only_odd_numbers%2 != 0 THEN
CALL sys.raiseapperror(3001, 'Not odd number!');
END IF;
END
go
DELIMITER ;
INSERT INTO ex1 VALUES(2);
INSERT INTO ex1 VALUES(3);
SELECT * FROM ex1;
CALL sys.getlasterror();
+-------------+-----------------+
| @error_code | @error_message |
+-------------+-----------------+
| 3001 | Not odd number! |
+-------------+-----------------+
|
|