MySQL BEFORE UPDATE 触发器 - 更改值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14774220/
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
MySQL BEFORE UPDATE trigger - change value
提问by Flock Dawson
So, I've got a MySQL table, named employees.
所以，我有一个 MySQL 表，名为员工。
ID name meta
0 Hyman ok
1 anne del
I want to write a trigger which prevents a row where meta='del' to update the meta field. So, if I do:
我想编写一个触发器来防止 meta='del' 更新元字段的行。所以，如果我这样做：
UPDATE employees SET meta = 'busy' WHERE ID = 0
The row should be updated and meta would be 'busy'
该行应该被更新，元将是“忙”
But when I do:
但是当我这样做时：
UPDATE employees SET meta = 'busy' WHERE ID = 1
The meta field should still be 'del'
元字段仍应为“del”
I tried:
我试过：
delimiter $$
CREATE TRIGGER updateEmployees
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.meta = 'del' THEN
NEW.meta = 'del'
END IF;
END$$
delimiter ;
But MySQL returns with an syntax error. Any ideas?
但是 MySQL 返回一个语法错误。有任何想法吗？
回答by hadess20
You forgot to add the SET clause. This way it doesn't actually change the value.
您忘记添加 SET 子句。这样它实际上不会改变值。
delimiter $$
CREATE TRIGGER updateEmployees
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.meta = 'del' THEN
SET NEW.meta = 'del';
END IF;
END$$
delimiter ;
回答by John Woo
you missed ;
delimiter $$
CREATE TRIGGER updateEmployees
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.meta = 'del' THEN
NEW.meta = 'del'; -- << here
END IF;
END$$
delimiter ;
你错过了 ;
delimiter $$
CREATE TRIGGER updateEmployees
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.meta = 'del' THEN
NEW.meta = 'del'; -- << here
END IF;
END$$
delimiter ;
TRIGGER IS EVIL.
触发器是邪恶的。
An alternative to trigger is by adding another condition AND
触发的替代方法是添加另一个条件 AND
UPDATE employees
SET meta = 'busy'
WHERE ID = 1 AND meta <> 'del'
