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…).
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.
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?
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
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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
I hope this helps someone else!
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.
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.