How to Abort or Allow MySQL INSERT Operations Using Triggers to Prevent Duplicate Entries?

Aborting MySQL INSERT Operations with Triggers

To prevent duplicate entries in a table based on both URL and parameter string, a trigger can be implemented. This trigger will evaluate whether an incoming INSERT operation would result in a duplicate and, if necessary, throw an exception or allow the insert accordingly.

Aborting the Operation

For the "... ABORT/throw exception to C# ..." part, you can use the SIGNAL statement:

SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = 'Duplicate entry: URL and parameter string already exists.';

This will raise a MySQLException that your C# code can catch.

Allowing the Insert

For the "... Allow insert ..." part, simply omit the SIGNAL statement. The insert will proceed as usual if no duplicate entry is detected.

Revised Trigger Code

Here is the modified trigger code incorporating both the abort logic and the allow insert logic:

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
   SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = 'Duplicate entry: URL and parameter string already exists.';
END
END

The above is the detailed content of How to Abort or Allow MySQL INSERT Operations Using Triggers to Prevent Duplicate Entries?. For more information, please follow other related articles on the PHP Chinese website!