問題描述
我有一個表，其中包含一個 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模板網！