mysql 自定义错误码,创建自定义错误消息MySQL

In MySQL, how to create custom message for this error message:

Cannot delete or update a parent row: a foreign key constraint fails

(database.jenis_fasum, CONSTRAINT jenis_fasum_ibfk_1 FOREIGN KEY

(id_kategori) REFERENCES kategori_fasum (id_kategori))

Using trigger maybe? Can someone provide an example?

Thanks for your help

解决方案

I don't think it can be done in a TRIGGER, but it can be done with a stored procedure in MySQL 5.5.

Here's the default error message:

mysql> INSERT INTO area SET location_id = 'invalid';

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`rates`.`area`, CONSTRAINT `area_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`))

mysql> SHOW ERRORS;

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Error | 1452 | Cannot add or update a child row: a foreign key constraint fails (`rates`.`area`, CONSTRAINT `area_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`)) |

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

And here's our stored procedure:

DROP PROCEDURE IF EXISTS test1;

DELIMITER //

CREATE PROCEDURE test1()

DETERMINISTIC

MODIFIES SQL DATA

SQL SECURITY INVOKER

BEGIN

DECLARE EXIT HANDLER FOR SQLSTATE '23000'

BEGIN

SIGNAL SQLSTATE '23000' SET

MYSQL_ERRNO = 1452,

MESSAGE_TEXT = 'Yo! Error 23000!';

END;

INSERT INTO area SET location_id = 'invalid';

END;

//

DELIMITER ;

And here's our custom error message:

mysql> CALL test1();

ERROR 1452 (23000): Yo! Error 23000!

mysql> SHOW ERRORS;

+-------+------+------------------+

| Level | Code | Message |

+-------+------+------------------+

| Error | 1452 | Yo! Error 23000! |

+-------+------+------------------+

1 row in set (0.00 sec)

By the way, why do you want this?