mysql如何在触发器和存储过程中捕获异常? [英] How to catch any exception in triggers and store procedures for mysql?

问题描述

我一直在尝试捕获mysql异常，特别是对于触发器和存储过程.我们如何从mysql端捕获异常?我仍然找不到任何解决方案.您的帮助将不胜感激.

谢谢 希特什

解决方案

因为这是我在触发器中搜索MySQL错误处理的顶部，所以我想与我分享MySQL 5.5+的解决方案

我的原始帖子: https://stackoverflow.com/a/26115231/1733365 在下面重复...... /p>

因为当我在MySQL触发器中搜索错误处理时，这篇文章排在顶部，所以我认为我会分享一些知识.

如果出现错误，则可以强制MySQL使用 SIGNAL ，但是如果您不将其指定为SQLEXCEPTION的类，则不会发生任何事情，因为并非所有SQLSTATE都被认为是错误的，即使如此，您也必须确保重新签名(如果您有嵌套的BEGIN/END块.

或者，甚至更简单地，在触发器中，声明退出处理程序并为异常重新签名.

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

如果您的身体发生错误，它将被扔回到顶部并错误退出.这也可以用在存储过程中.

这适用于5.5版以上的任何版本.

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+

My original post: https://stackoverflow.com/a/26115231/1733365 Duplicated below...

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+.

这篇关于mysql如何在触发器和存储过程中捕获异常?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！