如果条件存在，则 MySQL 触发器

2022-01-01 00:00:00 mysql triggers

我正在尝试编写一个更新触发器，它只会在更新语句中设置新密码时更新密码，但我在尝试确定语法时遇到了很糟糕的时间.这应该很简单，但我只是没有找到解决方案.

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 时被发送回，那么它不会为空或空白，并且 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 ;

相关文章