使用触发器停止插入或更新

2021-11-20 00:00:00 mysql triggers

既然 MySQL 忽略了检查约束，那么如何使用触发器来阻止插入或更新的发生?

Since MySQL ignores check constraints, how does one go about using a trigger to stop an insert or update from happening?

例如:

表foo有一个属性叫agency，该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.

相关文章