Question

如何Mysql触发器中抛出一个异常

Answer 1

从Mysql 5.5 开始为我们提供了SIGNAL函数来实现这个功能。
[sql] view plain copy
CREATE TRIGGER `tg_order_create` AFTER INSERT ON `tp_order` FOR EACH ROW BEGIN
DECLARE msg varchar(200);
/*冻结金额*/
IF 2=NEW.condition THEN
UPDATE `tp_user` SET `frozen_amount`=`frozen_amount`+NEW.amount WHERE `id`=NEW.uid AND `amount`-`frozen_amount` > NEW.amount;
/*如果余额不足,产生一个错误*/
IF ROW_COUNT() <> 1 THEN
set msg = "用户余额不足以完成支付.";
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
END IF;
/*扣除金额*/
ELSEIF 3=NEW.condition THEN
UPDATE `tp_user` SET `amount`=`amount`-NEW.amount WHERE `id`=NEW.uid AND `amount`-`frozen_amount` > NEW.amount;
/*如果余额不足,产生一个错误*/
IF ROW_COUNT() <> 1 THEN
set msg = "用户余额不足以完成支付.";
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
END IF;
END IF;
END;

这里这条触发器的功能是库存操作，当库存足够的时候 减少库存，否则 抛出一个异常并报告商品库存不足:

[sql] view plain copy
CREATE TRIGGER `TG_order_detail_dec_stock` BEFORE INSERT ON `tp_order_detail` FOR EACH ROW BEGIN
DECLARE msg VARCHAR(200);
UPDATE `tp_stock` SET `num`=`num`-NEW.num WHERE `goods_id`=NEW.goods_id AND `mid`=NEW.mid AND `num`>=NEW.num;
IF ROW_COUNT() <> 1 THEN
SELECT CONCAT(`name`, ' 库存不足.') INTO msg FROM `tp_goods` WHERE `id`=NEW.goods_id;
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
END IF;
END;

在PHP端的处理(注:使用ThinkPHP框架):

[php] view plain copy
//前面省略若干行....
try {

if (false === ($order_pk = $tbl_order->add($order))) {
$tbl->rollback();
echo json_encode(array('success' => -1, 'message' => "创建订单失败!."), JSON_UNESCAPED_UNICODE);
return;
}
}catch (\PDOException $e){
$errInfo=$e->errorInfo[2];
$tbl->rollback();
echo json_encode(array('success' => -1, 'message' => "创建订单失败!,{$errInfo}"), JSON_UNESCAPED_UNICODE);
return;
}
//后面省略若干行....