2

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;
Jason Aller
  • 3,541
  • 28
  • 38
  • 38
KerDam
  • 421
  • 4
  • 9
  • This trigger is for `UPDATE` not `INSERT`. – Blank Jul 26 '16 at 08:38
  • I think the only way to block an `INSERT` from MySQL would be to throw an exception, which isn't very nice. Is there some way you could block the `INSERT` from your app layer? – Tim Biegeleisen Jul 26 '16 at 08:39

1 Answers1

4

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.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Nice solution! But indeed, you do need a different error type. `SQLSTATE 01xxx` is a warning and doesn't cancel the operation. When in doubt, you can use `HY000`, which is the code for "generic exception". – okdewit Nov 12 '19 at 21:07
  • The error is not triggered. I implement it using https://stackoverflow.com/a/31613668/717267 – Eduardo Cuomo Jan 05 '22 at 15:39