MySQL trigger if condition exists

Question

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  lt  gt      THEN         SET NEW password   PASSWORD NEW password     END IF  END    I ve tried   IF  NEW password  lt  gt  NULL  THEN  IF  NEW password  THEN  IF NEW password  lt  gt  NULL THEN  IF  NEW password  gt  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

User · Accepted Answer

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.

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 ;

User · Answer

Try to do      DELIMITER            CREATE TRIGGER aumentarsalario          BEFORE INSERT          ON empregados         FOR EACH ROW         BEGIN           if  NEW SALARIO  lt  900  THEN               set NEW SALARIO   NEW SALARIO    NEW SALARIO   0 1             END IF          END      DELIMITER

[mysql] MySQL trigger if condition exists

Examples related to mysql

Examples related to triggers