导致插入失败的触发器？有可能吗？

Question

在清理this answer的过程中，我了解了一点MySQL中的TRIGGER和存储过程，但令人震惊的是，虽然BEFORE INSERT和BEFORE UPDATE触发器可以修改数据，但它们似乎不会导致插入/更新失败(即。验证)。在这种特殊情况下，我能够通过操作数据的方式使其工作，从而导致主键重复，这在这种特殊情况下是有意义的，但在一般意义上不一定是有意义的。

这种功能在MySQL中是可能的吗？在任何其他的关系数据库管理系统中(遗憾的是，我的经验仅限于MySQL )？也许是一种THROW EXCEPTION风格的语法？

Answer 1

从这个blog post

MySQL触发器:如何使用触发器中止插入、更新或删除？在EfNet的#mysql上，有人问：

如果我的业务规则失败，如何使触发器中止操作？

在MySQL 5.0和5.1中，您需要采取一些技巧来使触发器失败并传递有意义的错误消息。关于错误处理，MySQL存储过程常见问题解答如下：

SP 11. SP是否有“raise”语句来“引发应用程序错误”？对不起，目前还没有。SQL标准SIGNAL和RESIGNAL语句位于TODO上。

也许MySQL 5.2将包含SIGNAL语句，这将使这种直接从MySQL存储过程编程中窃取的攻击变得过时。这是怎么回事？您将强制MySQL尝试使用不存在的列。丑陋？是。它起作用了吗？好的。

CREATE TRIGGER mytabletriggerexample示例BEFORE INSERT FOR EACH ROW IF(NEW.important_value) <(奇特*丹西*计算) THEN dummy INT；SELECT您有意义的错误消息goes here INTO dummy FROM mytable WHERE mytable.id=new.id END IF；END;

Answer 2

这是我做的方法。请注意SET NEW='some error';。MySQL会告诉您“变量'new‘不能设置为值’错误:无法删除此项目。sales表中有此项目的记录。‘”

您可以在代码中捕获此错误，然后显示产生的错误:)

DELIMITER $$
DROP TRIGGER IF EXISTS before_tblinventoryexceptionreasons_delete $$
CREATE TRIGGER before_tblinventoryexceptionreasons_delete
BEFORE DELETE ON tblinventoryexceptionreasons
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblinventoryexceptionreasons = old.idtblinventoryexceptionreasons) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exception reasons table with this item.';
  END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS before_storesalesconfig_delete $$
CREATE TRIGGER before_storesalesconfig_delete
BEFORE DELETE ON tblstoresalesconfig
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblstoresales WHERE tblstoresales.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the sales table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventory WHERE tblinventory.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exceptions table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$$
DELIMITER ;

DELIMITER $$
DROP TRIGGER IF EXISTS before_tblinvoice_delete $$
CREATE TRIGGER before_tblinvoice_delete
BEFORE DELETE ON tblinvoice
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblinvoice = old.idtblinvoice) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$$
DELIMITER ;

Answer 3

因为在搜索MySQL触发器中的错误处理时，这篇文章排在首位，所以我想我应该分享一些知识。

如果出现错误，可以强制MySQL使用SIGNAL，但是如果不将其指定为SQLEXCEPTION类，那么什么都不会发生，因为并非所有SQLSTATE都被认为是坏的，即使这样，如果有任何嵌套的BEGIN/END块，也必须确保使用RESIGNAL。

或者，也可能更简单，在触发器中声明一个退出处理程序并重新发送异常信号。

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