MySQL 导致 INSERT 失败的触发器？可能的？

TRIGGERs that cause INSERTs to fail? Possible?

提问by Matthew Scharley

In cleaning up this answerI learnt a bit about TRIGGERs and stored procedures in MySQL, but was stunned that, while BEFORE INSERTand BEFORE UPDATEtriggers could modify data, they seemingly couldn't cause the insert/update to fail (ie. validation). In this particular case I was able to get this to work by manipulating the data in such a way as to cause a primary key duplicate, which in this particular case made sense, but doesn't necessarily make sense in a general sense.

在清理这个答案我了解了一些关于TRIGGERS和MySQL的存储过程，但被惊呆了，虽然BEFORE INSERT和BEFORE UPDATE触发器可以修改数据，他们似乎无法引起插入/更新失败（即验证）。在这种特殊情况下，我能够通过以导致主键重复的方式操作数据来使其工作，这在这种特殊情况下是有道理的，但在一般意义上不一定有意义。

Is this sort of functionality possible in MySQL? In any other RDBMS (my experience is limited to MySQL sadly)? Perhaps a THROW EXCEPTIONstyle syntax?

在 MySQL 中可以实现这种功能吗？在任何其他 RDBMS 中（遗憾的是，我的经验仅限于 MySQL）？也许是一种THROW EXCEPTION风格语法？

回答by Esteban Brenes

From this blog post

从这篇博文

MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger? On EfNet's #mysql someone asked:

How do I make a trigger abort the operation if my business rule fails?

In MySQL 5.0 and 5.1 you need to resort to some trickery to make a trigger fail and deliver a meaningful error message. The MySQL Stored Procedure FAQ says this about error handling:

SP 11. Do SPs have a “raise” statement to “raise application errors”? Sorry, not at present. The SQL standard SIGNAL and RESIGNAL statements are on the TODO.

Perhaps MySQL 5.2 will include SIGNAL statement which will make this hack stolen straight from MySQL Stored Procedure Programming obsolete. What is the hack? You're going to force MySQL to attempt to use a column that does not exist. Ugly? Yes. Does it work? Sure.

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;

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

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

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

SP 11. SP 是否有“提出”声明来“提出应用程序错误”？抱歉，目前没有。SQL 标准 SIGNAL 和 RESIGNAL 语句位于 TODO 上。

也许 MySQL 5.2 将包含 SIGNAL 语句，这将使直接从 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;

回答by PCPGMR

Here is the way I did it. Note the SET NEW='some error';. MySQL will tell you "Variable 'new' can't be set to the value of 'Error: Cannot delete this item. There are records in the sales table with this item.'"

这是我这样做的方式。请注意SET NEW='some error';. MySQL会告诉你“变量'new'不能设置为'错误：不能删除这个项目。销售表中有这个项目的记录。'”

You can trap this in your code and then show the resulting error :)

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

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 ;

回答by Kyle Johnson

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

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

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 RESIGNALif you have any nested BEGIN/END blocks.

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

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

这适用于任何 5.5+ 版本。

回答by Karel Ruland

Doen't work in triggers (Dynamic SQL is not allowed in stored function or trigger)

不适用于触发器（存储函数或触发器中不允许使用动态 SQL）

I use a very dirty solution:

我使用了一个非常脏的解决方案：

If NEW.test=1 then CALL TEST_CANNOT_BE_SET_TO_1; end if;

If NEW.test=1 then CALL TEST_CANNOT_BE_SET_TO_1; end if;

When test=1 Mysql throws the following exception:

当 test=1 Mysql 抛出如下异常：

PROCEDURE administratie.TEST_CANNOT_BE_SET_TO_1 does not exist

PROCEDURE administratie.TEST_CANNOT_BE_SET_TO_1 不存在

Not sophisticated but fast and usefull.

不复杂，但快速且有用。

回答by Ilya Kochetov

This will abort your INSERT by raising an exception (from http://www.experts-exchange.com/Database/MySQL/Q_23788965.html)

这将通过引发异常来中止您的 INSERT（来自http://www.experts-exchange.com/Database/MySQL/Q_23788965.html）

DROP PROCEDURE IF EXISTS `MyRaiseError`$$

CREATE PROCEDURE `MyRaiseError`(msg VARCHAR(62))
BEGIN
DECLARE Tmsg VARCHAR(80);
SET Tmsg = msg;
IF (CHAR_LENGTH(TRIM(Tmsg)) = 0 OR Tmsg IS NULL) THEN
SET Tmsg = 'ERROR GENERADO';
END IF;
SET Tmsg = CONCAT('@@MyError', Tmsg, '@@MyError');
SET @MyError = CONCAT('INSERT INTO', Tmsg);
PREPARE stmt FROM @MyError;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

Usage:

用法：

call MyRaiseError('Here error message!');

回答by Ilya Kochetov

in MS SQL you could make it work using proper syntax:

在 MS SQL 中，您可以使用正确的语法使其工作：

IF UPDATE(column_name)
BEGIN
  RAISEERROR
  ROLLBACK TRAN
  RETURN
END

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx