mysql触发器调用存储过程出错,mysql如何捕获触发器和存储过程中的任何异常？...

I have been trying to catch mysql exception especially for triggers and store procedures.How can we catch the exception from mysql side?. I still not found any solution. your help would be appreciate.

Thanks

Hitesh

解决方案

Because this comes up in the top of my search for MySQL error handling in triggers, I thought I'd share my solution for MySQL 5.5+

Because this article comes up towards the top when I search for error handling in MySQL triggers, I thought I'd share some knowledge.

If there is an error, you can force MySQL to use a SIGNAL, but if you don't specify it as a class as SQLEXCEPTION, then nothing will happen, as not all SQLSTATEs are considered bad, and even then you'd have to make sure to RESIGNAL if you have any nested BEGIN/END blocks.

Alternatively, and probably simpler still, within your trigger, declare an exit handler and resignal the exception.

CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` FOR EACH ROW

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

RESIGNAL;

DECLARE EXIT HANDLER FOR SQLWARNING

RESIGNAL;

DECLARE EXIT HANDLER FOR NOT FOUND

RESIGNAL;

-- Do the work of the trigger.

END

And if in your body there occurs an error, it will be thrown back up to the top and exit with an error. This can also be used in stored procedures and whatnot.

This works with anything version 5.5+.