使用触发器停止插入或更新 [英] Use a trigger to stop an insert or update
本文介绍了使用触发器停止插入或更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
既然MySQL忽略了检查约束,那么如何使用触发器来阻止插入或更新发生呢?
Since MySQL ignores check constraints, how does one go about using a trigger to stop an insert or update from happening?
例如:
表foo具有一个称为agency的属性,该代理商的属性只能是1、2、3、4或5.
Table foo has an attribute called agency, and the agency attribute can only be 1, 2, 3, 4, or 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 ;
还是有更好的方法在MySQL中执行检查约束?
Or is there a better way to go about doing check constraints in MySQL?
推荐答案
尝试使用SIGNAL语法- https://dev.mysql.com/doc/refman/5.5/en/signal.html
Try the SIGNAL syntax - 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
编辑
根据下面的Bill Karwin的流行评论进行了更新.
Updated based on popular comment below by Bill Karwin.
这篇关于使用触发器停止插入或更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文