如何在 MySql 触发器中中止 INSERT 操作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2538786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to abort INSERT operation in MySql trigger?
提问by johnrl
I have a table containing an url and a string representing its parameters. The problem is I want an url and a parameterstring to be the unique constraint for the table - aka no entries can have the same url AND parameter string. The parameter string can be of arbitrary length (longer than 800bytes or so which is the max length for a MySql key, so I cant use Unique(url, params) since it throws an error...).
我有一个表，其中包含一个 url 和一个表示其参数的字符串。问题是我想要一个 url 和一个参数字符串作为表的唯一约束 - 也就是没有条目可以具有相同的 url AND 参数字符串。参数字符串可以是任意长度（大于 800 字节左右，这是 MySql 键的最大长度，所以我不能使用 Unique(url, params) 因为它会引发错误......）。
I thought about using triggers to do this, but how do I throw an exception/raise an error if the trigger discovers the insert is about to insert a duplicate entry? I imagine I would like to have a MySqlException thrown like MySql does with duplicate primary keys etc so I can catch it in my C# code.
我考虑过使用触发器来执行此操作，但是如果触发器发现插入即将插入重复条目，我该如何抛出异常/引发错误？我想我想像 MySql 那样抛出一个 MySqlException 并使用重复的主键等，这样我就可以在我的 C# 代码中捕获它。
I have two pieces in the trigger I need to get help with: ... Abort throw exception to C# ... How do I throw an exception etc to C#? ... Allow insert ... - how do I just allow the insert if there is no duplicate entry?
我在触发器中有两部分需要帮助：...中止向 C# 抛出异常...如何向 C# 抛出异常等？... 允许插入 ... - 如果没有重复条目，我如何只允许插入？
Heres the trigger code:
触发代码如下：
CREATE TRIGGER urls_check_duplicates
BEFORE INSERT ON urls
FOR EACH ROW
BEGIN
DECLARE num_rows INTEGER;
SELECT COUNT(*)
INTO num_rows
FROM urls
WHERE url = NEW.url AND params = NEW.params;
IF num_rows > 0 THEN
... ABORT/throw exception to C# ...
ELSE
... Allow insert ...
END
采纳答案by soulmerge
The comments in the mysql documentation about triggerssuggest that there is no such feature in mysql. The best you can do is to create a separate table for your trigger errors, as suggested on the same page.
mysql 文档中关于触发器的注释表明mysql中没有这样的功能。您可以做的最好的事情是为您的触发器错误创建一个单独的表，如同一页面上的建议。
回答by GazB
I came across this and although the solution works I later came across what feels to me like a better solution. I suspect this wasn't an option when this question was originally answered.
我遇到了这个问题，尽管该解决方案有效，但后来我发现了一个更好的解决方案。我怀疑最初回答这个问题时这不是一个选项。
CREATE TRIGGER `TestTable_SomeTrigger`
BEFORE UPDATE ON `test_table`
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(255);
IF (SomeTestToFail = "FAIL!") THEN
set msg = "DIE: You broke the rules... I will now Smite you, hold still...";
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;
-- Do any other code here you may want to occur if it's all OK or leave blank it will be
-- skipped if the above if is true
END$$
This will now return a nice (or evil!) error message that you can trap. For more info on this see: http://dev.mysql.com/doc/refman/5.5/en/signal.html
现在这将返回一个好的（或邪恶的！）错误消息，您可以捕获它。有关更多信息，请参阅：http: //dev.mysql.com/doc/refman/5.5/en/signal.html
I hope this helps someone else!
我希望这对其他人有帮助！
回答by g33kz0r
If your table definition is for a NOT NULL value, you could set NEW to NULL, which would effectively not do the insert. You might have to turn strict sql mode on for this to work properly.
如果您的表定义是 NOT NULL 值，您可以将 NEW 设置为 NULL，这实际上不会执行插入操作。您可能必须打开严格的 sql 模式才能正常工作。
