导致INSERT失败的TRIGGER？可能？

在清理这个答案时,我TRIGGER在MySQL 中学到了一些关于s和存储过程的知识,但我惊讶地发现,虽然BEFORE INSERT和BEFORE UPDATE触发器可以修改数据,但它们似乎不会导致插入/更新失败(即验证).在这种特殊情况下,我能够通过操纵数据来使这一点工作,从而导致主键复制,在这种特殊情况下,这是有意义的,但在一般意义上并不一定有意义.

这种功能在MySQL中是否可行？在任何其他RDBMS(我的经验仅限于MySQL)？也许是一种THROW EXCEPTION风格语法？

来自这篇博文

MySQL触发器:如何使用触发器中止INSERT,UPDATE或DELETE？在EfNet的#mysql上有人问:

如果业务规则失败,如何触发操作会中止操作？

在MySQL 5.0和5.1中,您需要采取一些技巧来使触发器失败并提供有意义的错误消息.MySQL存储过程常见问题解答说明了错误处理:

SP 11. SP是否有"提高"声明以"引发应用程序错误"？对不起,现在不行.SQL标准SIGNAL和RESIGNAL语句位于TODO上.

也许MySQL 5.2将包含SIGNAL语句,这将使得这个hack直接从MySQL存储过程编程中被淘汰.什么是黑客？您将强制MySQL尝试使用不存在的列.丑陋？是.它有用吗？当然.

CREATE TRIGGER mytabletriggerexample
BEFORE INSERT
FOR EACH ROW BEGIN
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF; END;

这是我做的方式.请注意SET NEW='some error';.MySQL会告诉你"变量'新'不能设置为'错误:无法删除此项.在销售表中有这个项目的记录.'"

您可以在代码中捕获它,然后显示生成的错误:)

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 ;