How to force use restriction in mysql?

Question

I have a table T with two fields A and B of type integer.

Now I want to ensure that A is less than or equal to B.

Do I need to create a “before insert / update” trigger?

And if so, how to make the trigger fail?

+1

Raffael Jun 2 '11 at 17:19

1 answer

Alain collins · Accepted Answer · 2011-06-02T23:14:01+0000

You can do this in your application code, but the safest way to do this is in the database using a trigger.

To abort the insertion, create an error in the trigger. We are doing something like this:

CREATE TRIGGER `t_insert` BEFORE UPDATE ON `t` FOR EACH ROW BEGIN IF new.A > new.B THEN CALL NONEXISTENT_PROC() END IF; END