MySQL触发具有精确值的触发器
我创建了一个MySQL数据库和几个表。我正在尝试设置一个触发器，以触发计数不等于一组定义的值，以拒绝插入称为房间的表中的插入。 如果计数不完全匹配1、2或3，则此触发器应拒绝插入。这些是唯一允许的值。
我的触发器目前看起来像这样：
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
语法是正确的，因为MySQL Workbench不抱怨。但是，即使我在表中的插入包括1、2或3的值，它总是拒绝它，而且我似乎无法理解原因。 我还尝试了：
IF NEW.count NOT BETWEEN 1 AND 3 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'count must be 1, 2, or 3.';
END IF;
这确实有效，但是它允许将小数插入桌子，或者将小数插入小数而不是拒绝。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您写道：
无论新价值是什么。将真实值与
或结合起来，即使其他术语是错误的，整个表达式也使整个表达式成真。示例：假设new.count是给定行的1。因此，该表达式是：
因为如果new.count为1，那么它绝对不是2或3。
您应该使用：
You wrote:
No matter what the value of NEW.count, at least two of those terms will be true. Combining a true value with
ORmakes 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:
Because if NEW.count is 1, then it is definitely not 2 or 3.
You should have used: