保护列，禁止更新，仅当 MySQL 中为 NULL 时才允许插入 [英] Protect column, disallow update, only allow insert if NULL in MySQL
问题描述
我想保护日期列中的现有日期不被覆盖.因此，不允许更新日期列，并且仅当现有字段值为 NULL(日期列默认值为 NULL)时才允许插入.触发器 是在 MySQL 中完成此操作的唯一方法吗?如果是这样，下面的触发器会起作用吗?
I want to protect existing dates in a date column from being overwritten. So disallow updates to the date column and only allow inserts if the existing field value is NULL (date column default is NULL). Are triggers the only way to accomplish this in MySQL? If so, would the trigger below work?
create trigger date_check
before insert, update on date
for each row
begin
if(date IS NOT NULL) then
SIGNAL 'date already set'
end if ;
end ;
背景:我有一个包含关键日期的表格，但由于用户错误而意外更改.我在用户界面中进行了一些检查以防止这种情况再次发生，但如果可能的话，我希望直接与数据库一起使用另一层安全性.
Background: I have a table with critical dates that was accidentally changed due to user error. I put some checks in the user interface to prevent this from happening again but want another layer of safety directly with the database if possible.
推荐答案
是的，在 MySQL 中触发器是唯一的方法.MySQL 不支持约束.
Yes, in MySQL triggers are the only way to do this. MySQL does not support constraints.
您的触发器不完全正确.首先，您有 Your trigger is not exactly right. First, you have 这种情况下的 An 这篇关于保护列，禁止更新，仅当 MySQL 中为 NULL 时才允许插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！update on date，但这应该是 update on .其次，您正在检查用于更新的日期值.也许你的意思是:
update on date, but this should be update on <table name>. Second, you are checking the date value used for the update. Perhaps you mean:create trigger date_check_update
before update on <the table name goes here>
for each row
begin
if (old.date IS NOT NULL) then
SIGNAL 'date already set'
end if ;
end;
insert 触发器没有意义.insert trigger on this condition doesn't make sense.
登录
关闭
