MySQL触发器如果条件存在 [英] MySQL trigger if condition exists

问题描述

我正在尝试编写一个更新触发器，该触发器仅在在update语句中设置了新密码时才会更新密码，但是我在确定语法方面花费了很多时间.这应该很容易，但是我只是没有找到解决方法.

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.

这是我的代码:

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;

我尝试过:

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?

推荐答案

我认为您的意思是在未提供新密码时将其更新回OLD密码.

I think you mean to update it back to the OLD password, when the NEW one is not supplied.

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，则该字段不会为null或为空，并且MySQL将尝试在其上重做Password()函数.要检测到此情况，请改用此代码

---

If the password field (already encrypted) is being sent back in the update to mySQL, then it will not be null or blank, and MySQL will attempt to redo the Password() function on it. To detect this, use this code instead

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 ;

这篇关于MySQL触发器如果条件存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！