title: Detecting if OLD value is not equal to NEW value and OLD value was NULL
body: <p>I would like insert a row into audit_field when the OLD value is not equal to the NEW value.  To do so, I added <code>IF NEW.notes &lt;&gt; OLD.notes THEN</code> before each of the inserts.  I found (I think), however, that if the OLD value is NULL, then the insert isn't performed.  I haven't tested whether the opposite is true, and it will not insert if the OLD value is something and the NEW value is NULL, but I expect it will not.</p>
  
  <p>How do I detect if the OLD value is not equal to NEW value and OLD value was NULL (or similarly, if the NEW value is NULL)?</p>
  
  <pre><code>CREATE TRIGGER tg_students_upd AFTER UPDATE ON students
  FOR EACH ROW 
  BEGIN
      IF NEW.name &lt;&gt; OLD.name OR NEW.ssn &lt;&gt; OLD.ssn OR NEW.notes &lt;&gt; OLD.notes THEN
          INSERT INTO audits(tableName,pk,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('students', @AID, 'u', NOW(), @users_id, USER(), @requesting_ip );
          SET @AID=LAST_INSERT_ID();
          IF NEW.name &lt;&gt; OLD.name THEN
              INSERT INTO audit_field(audits_id,columnName,oldValue,newValue) VALUES (@AID,'name',OLD.name,NEW.name);
          END IF;
          IF NEW.ssn &lt;&gt; OLD.ssn THEN
              INSERT INTO audit_field(audits_id,columnName,oldValue,newValue) VALUES (@AID,'ssn',OLD.ssn,NEW.ssn);
          END IF;
          IF NEW.notes &lt;&gt; OLD.notes THEN
              INSERT INTO audit_field(audits_id,columnName,oldValue,newValue) VALUES (@AID,'notes',OLD.notes,NEW.notes);
          END IF;
      END IF;
  END$$
  </code></pre>