CODEWITHSUNDEEP
mysqltriggersinsert
KerDam
KerDam

Reputation: 421

MySQL test before insert and cancel if condition is met

I would like to create a trigger that tests a condition before insertion and if the condition is met then cancels the insert.

I came across this code in the manual which 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;

Upvotes: 2

Views: 1975

Answers (1)

e4c5
e4c5

Reputation: 53734

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.

Upvotes: 4

Related Questions