如果条件存在，则 MySQL 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5372872/
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 trigger if condition exists
提问by gurun8
I'm trying to write an update trigger that will only update a password when a new password is set in the update statement but I'm having a terrible time trying to nail down the syntax. This should be a no-brainer but I'm just not finding the solution.
我正在尝试编写一个更新触发器，它只会在更新语句中设置新密码时更新密码，但我在尝试确定语法时遇到了很糟糕的时间。这应该很容易，但我只是没有找到解决方案。
Here's my code:
这是我的代码：
CREATE TRIGGER upd_user BEFORE UPDATE ON `user`
FOR EACH ROW BEGIN
IF (NEW.password <> '') THEN
SET NEW.password = PASSWORD(NEW.password);
END IF;
END;
I've tried:
我试过了：
IF (NEW.password <> NULL) THEN
IF (NEW.password) THEN
IF NEW.password <> NULL THEN
IF (NEW.password > 0) THEN
IF (NEW.password != NULL) THEN
And I'm sure many other combinations but it's just not working. Does anyone have any insight?
而且我确定还有许多其他组合，但它不起作用。有没有人有任何见解？
回答by RichardTheKiwi
I think you mean to update it back to the OLDpassword, when the NEW one is not supplied.
我认为您的意思OLD是在未提供新密码时将其更新回密码。
DROP TRIGGER IF EXISTS upd_user;
DELIMITER $$
CREATE TRIGGER upd_user BEFORE UPDATE ON `user`
FOR EACH ROW BEGIN
IF (NEW.password IS NULL OR NEW.password = '') THEN
SET NEW.password = OLD.password;
ELSE
SET NEW.password = Password(NEW.Password);
END IF;
END$$
DELIMITER ;
However, this means a user can never blank out a password.
但是，这意味着用户永远不能清空密码。
如果密码字段（已经加密）在更新到 mySQL 时被发送回，那么它不会为空或空白，并且 MySQL 将尝试在其上重做 Password() 函数。要检测到这一点，请改用此代码
DELIMITER $$
CREATE TRIGGER upd_user BEFORE UPDATE ON `user`
FOR EACH ROW BEGIN
IF (NEW.password IS NULL OR NEW.password = '' OR NEW.password = OLD.password) THEN
SET NEW.password = OLD.password;
ELSE
SET NEW.password = Password(NEW.Password);
END IF;
END$$
DELIMITER ;
回答by Lucas Brogni
Try to do...
试着做...
DELIMITER $$
CREATE TRIGGER aumentarsalario
BEFORE INSERT
ON empregados
FOR EACH ROW
BEGIN
if (NEW.SALARIO < 900) THEN
set NEW.SALARIO = NEW.SALARIO + (NEW.SALARIO * 0.1);
END IF;
END $$
DELIMITER ;
