如何在 MySql 触发器中中止 INSERT 操作?

问题描述

我有一个表，其中包含一个 url 和一个表示其参数的字符串.问题是我想要一个 url 和一个参数字符串作为表的唯一约束 - 也就是没有条目可以具有相同的 url AND 参数字符串.参数字符串可以是任意长度(大于 800 字节左右，这是 MySql 键的最大长度，所以我不能使用 Unique(url, params) 因为它会引发错误......).

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

我考虑过使用触发器来执行此操作，但是如果触发器发现插入即将插入重复条目，我该如何抛出异常/引发错误?我想我想像 MySql 那样抛出一个 MySqlException 并使用重复的主键等，这样我就可以在我的 C# 代码中捕获它.

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.

我的触发器中有两部分需要帮助:... 中止向 C# 抛出异常 ... 如何向 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?

触发代码如下:

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

推荐答案

mysql 文档中关于触发器的评论 表明 mysql 中没有这样的功能.您能做的最好的事情是为您的触发器错误创建一个单独的表，如同一页面上的建议.

The comments in the mysql documentation about triggers suggest 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 触发器中中止 INSERT 操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！