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 <> '') 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?

Jeff Puckett · Accepted Answer · 2017-11-19 03:10:34Z

52

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 ;

edited Nov 19, 2017 at 3:10

Jeff Puckett

41.6k19 gold badges125 silver badges177 bronze badges

answered Mar 21, 2011 at 1:30

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

ypercubeᵀᴹ Over a year ago

I think he means to implement the password encryption in this trigger.

RichardTheKiwi Over a year ago

@yper - I left that out but the part I was attacking was the fact that to preserve the old, you actually have to set it back within the trigger

ypercubeᵀᴹ Over a year ago

He is probably not correctly using delimiter, like in his previous question :)

ypercubeᵀᴹ Over a year ago

You are right though, your approach makes more sense. His code would encrypt all passwords unless they are blank, and allow blanks (unencrypted).

RichardTheKiwi Over a year ago

@yper - that didn't occur to me. Without the delimiter being set, the code would complain on the first occurence of ';' (the SET line) no matter how it is written!

|

Greg Glockner · Accepted Answer · 2020-02-13 03:19:31Z

-1

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 ;

edited Feb 13, 2020 at 3:19

Greg Glockner

5,6732 gold badges23 silver badges23 bronze badges

answered May 17, 2018 at 20:51

Lucas Brogni

1296 bronze badges

Collectives™ on Stack Overflow

MySQL trigger if condition exists

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related