MySQL Triggers with Exact Values-CodePudding

I have created a MySQL database and several tables. I am trying to setup a trigger for when the count is not equal to a set of defined values to reject an insertion into a table called ROOM. This trigger should reject an insert if the count does not exactly match 1, 2 or 3. Those are the only values allowed.

My trigger currently looks like this:

BEGIN
    IF NEW.`count` != 1 OR NEW.`count` != 2 OR NEW.`count` != 3 THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'ROOM insert rejected: count must be 1, 2, or 3.';
    END IF; 
END

The syntax is correct as MySQL workbench does not complain. However, even if my insertion into the table includes a value of 1, 2 or 3 it always rejects it and I cannot seem to understand why. I have also tried:

IF NEW.count NOT BETWEEN 1 AND 3 THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'count must be 1, 2, or 3.';
    END IF;

And this does work, however it allows for decimals to be inserted into the table, or rounds decimals up rather than rejecting it.

CodePudding user response：

You wrote:

IF NEW.`count` != 1 OR NEW.`count` != 2 OR NEW.`count` != 3 THEN
  ...

No matter what the value of NEW.count, at least two of those terms will be true. Combining a true value with OR makes the whole expression true, even if the other terms are false.

Example: suppose NEW.count is 1 for a given row. The expression is therefore:

IF false OR true OR true THEN
  ...

Because if NEW.count is 1, then it is definitely not 2 or 3.

You should have used:

IF NEW.`count` != 1 AND NEW.`count` != 2 AND NEW.`count` != 3 THEN
  ...