如果条件存在则 MySQL 触发

Question

我正在尝试编写一个更新触发器，该触发器仅在更新语句中设置新密码时才更新密码，但我在尝试确定语法时遇到了困难。这应该是理所当然的，但我只是没有找到解决方案。

这是我的代码：

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

我确信还有很多其他组合，但它只是不起作用。有人有任何见解吗？

Answer 1

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

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 ;

但是，这意味着用户永远无法删除密码。

---

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 ;

Answer 2

尝试做...

 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 ;