How to abort INSERT operation in MySql trigger?

Question

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 

Answer 1

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!