Detecting if OLD value is not equal to NEW value and OLD value was NULL

mysql, sql, triggers

Solution

Use `<=>`

SELECT NOT 1 <=> 1,NOT NULL <=> NULL, NOT 1 <=> NULL, NOT 1 <=> 2, 1 <> 1, NULL <> NULL, 1 <> NULL, 1 <>2;

+-------------+-------------------+----------------+-------------+--------+--------------+-----------+-------+
| NOT 1 <=> 1 | NOT NULL <=> NULL | NOT 1 <=> NULL | NOT 1 <=> 2 | 1 <> 1 | NULL <> NULL | 1 <> NULL | 1 <>2 |
+-------------+-------------------+----------------+-------------+--------+--------------+-----------+-------+
|           0 |                 0 |              1 |           1 |      0 | NULL         | NULL      |     1 |
+-------------+-------------------+----------------+-------------+--------+--------------+-----------+-------+

PS. Sorry, should have read the manual before posting a question, but hopefully it will help someone else.

Problem

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 `IF NEW.notes <> OLD.notes THEN` 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. 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)? ``` CREATE TRIGGER tg_students_upd AFTER UPDATE ON students FOR EACH ROW BEGIN IF NEW.name <> OLD.name OR NEW.ssn <> OLD.ssn OR NEW.notes <> 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 <> OLD.name THEN INSERT INTO audit_field(audits_id,columnName,oldValue,newValue) VALUES (@AID,'name',OLD.name,NEW.name); END IF; IF NEW.ssn <> OLD.ssn THEN INSERT INTO audit_field(audits_id,columnName,oldValue,newValue) VALUES (@AID,'ssn',OLD.ssn,NEW.ssn); END IF; IF NEW.notes <> OLD.notes THEN INSERT INTO audit_field(audits_id,columnName,oldValue,newValue) VALUES (@AID,'notes',OLD.notes,NEW.notes); END IF; END IF; END$$ ```

Original source