0

SETUP

A very simple table with 2 columns. (UserName, Password)

In this table I have a Before Update trigger:

BEGIN
    SET NEW.Password = SHA2(NEW.Password,256);
END

Problem

When ever I update the username AND password, all is great. BUT when I try to update just the username:

UPDATE `UserCreds` SET `UserName`= 'BOB'
WHERE `UserID`= 156

the password Also changes.

Question

How do I get the password field to update ONLY when the update statement contains

SET `Password` = 'supersafepassword'

What I tried

I tried a few variations of the following but everything still seems to evaluate to true, and the password still changes.

BEGIN
  IF NEW.Password IS NOT NULL THEN
    SET NEW.Password = SHA2(NEW.Password,256);
  END IF;
END

So i guess the real question is, What does the value of NEW.Password equal when the Update statement doesn't set it equal to anything?

In Short

If I don't Set the password in my update statement i don't want my password to change (as in changing username only)... but if I DO set it, I want it to be encrypted before saving.

  • 2
    You can compare NEW.Password with OLD.Password, e.g. IF NEW.Password <> OLD.Password THEN – a1ex07 Feb 3 '17 at 17:56
  • True.. but in the scenario described of updating JUST the UserName.. NEW.Password is not Set/Input/available/or Known. so that would always evaluate to true. – DarbyM Feb 3 '17 at 18:05
  • @a1ex07 Well, that does not yet make sense to me.. but Comparing New to Old when New is not set works. I'm gonna have to assume that if you do not set a column to a value in an update statement, that MySQL sets the NEW.column value = OLD.column value for you.?? So in my case, NEW will always = OLD, UNLESS I set it to something. good answer... Please post it as an answer with a description of why that works, and I will accept it as the answer. – DarbyM Feb 3 '17 at 18:24
2

Maybe mysql documentation is not so clear, but NEW is in fact refers to pseudorecord which contains new values (let's ignore access right for simplicity). It's probably more accurate to say that it gives you access to the row as it will be stored in the table , whereas OLD gives access to the row as it was right before UPDATE/DELETE executed.
Keeping that in mind, comparision in trigger body OLD.PASSWORD <> NEW.PASSWORD will be true (assuming "password" field is not null, otherwise you need to use IFNULL or something similar) only if PASSWORD field was actually changed by UPDATE statement.

  • Simple null-safe inequality comparison would be IF NOT (NEW.Password <=> OLD.Password) THEN ... – Michael - sqlbot Feb 5 '17 at 7:56
1

"In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated." -- https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

As I read that, the value of password would initially be the same in OLD and NEW. So

IF (NEW.password != OLD.password) THEN
    SET NEW.password = SHA2(NEW.Password,256);
ENDIF
  • Yes I understand you can compare NEW to OLD in a before update.. The question is, what does NEW.password equal when it is not set in the update statement? Does it equal Null? ''? Or does MySQL set NEW.Password = OLD.Password if the update statement does not set it? While Via @a1ex07 suggesion I have tested and it appears that IS how it functions... No where can I find in the documentation that is indeed what is going on though. – DarbyM Feb 3 '17 at 20:04
  • 1
    The quote says it is the "column after updating", which I take to mean the same as it was "before updating". (But, I could be misinterpreting.) Suggest you file a bug at http:/bugs.mysql.com asking for clarification in that page. – Rick James Feb 3 '17 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.