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

Question

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

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

Answer 1

从此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;