MySQL 使用触发器停止插入或更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10168408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use a trigger to stop an insert or update
提问by Ryan
Since MySQL ignores check constraints, how does one go about using a trigger to stop an insert or update from happening?
由于 MySQL 忽略检查约束，如何使用触发器来阻止插入或更新的发生？
For example:
例如：
Table foo has an attribute called agency, and the agency attribute can only be 1, 2, 3, 4, or 5.
表foo有一个属性叫agency，该agency属性只能是1、2、3、4或5。
delimiter $$
create trigger agency_check
before insert on foo
for each row
begin
if (new.agency < 1 or new.agency > 5) then
#Do nothing?
end if;
end
$$
delimiter ;
Or is there a better way to go about doing check constraints in MySQL?
或者有没有更好的方法来在 MySQL 中进行检查约束？
回答by Naveen Kumar
Try the SIGNAL syntax - https://dev.mysql.com/doc/refman/5.5/en/signal.html
尝试信号语法 - https://dev.mysql.com/doc/refman/5.5/en/signal.html
create trigger agency_check
before insert on foo
for each row
begin
if (new.agency < 1 or new.agency >5) then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'your error message';
end if
end
EDIT
编辑
Updated based on popular comment below by Bill Karwin.
根据比尔·卡文 (Bill Karwin) 下面的流行评论更新。
回答by bradoaks
If your version of MySQL is older than 5.5, try setting a non-null field of the table to NULL. It is a hack, but it does prevent the update or insert from completing.
如果您的 MySQL 版本早于 5.5，请尝试将表的非空字段设置为 NULL。这是一个黑客，但它确实阻止更新或插入完成。
The SIGNAL command which Naveen suggests looks great, and I'm looking forward to using it after we upgrade.
Naveen 建议的 SIGNAL 命令看起来很棒，我期待在我们升级后使用它。
