Mysql – Value of an UNSET NEW.Column in Before Update Trigger

MySQLtrigger

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.

Best Answer

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.

Related Question