KerDam KerDam - 4 months ago 17
SQL Question

MySQL test before insert and cancel if condition is met

I would like to create a trigger that test a condition before insert and if the condition is met then cancel the insert.

I came across this code in the manual wich contains if statement but no ways to cancel the insert is specified in the documentation.

CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;

Answer

You have to use a signal

SIGNAL is the way to “return” an error. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. Also, it provides control over the error's characteristics (error number, SQLSTATE value, message).

Thus you can have

CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
           ELSE
               SIGNAL SQLSTATE '01000'
                 SET MESSAGE_TEXT = 'Sorry cannot insert', MYSQL_ERRNO = 1000;
    ->     END IF;
    -> END;

Update: You might also want to take a look at the error messages list and choose a more appropriate error number.