MySQL NULL processing after updating a trigger that only works when data changes

Question

Due to reputation limitations, this is the next question from this answer to a previous question . I would like to know if there is a more efficient way to check each field for changes when handling NULL values.

CREATE TABLE foo ( a INT NULL DEFAULT NULL, b INT NULL DEFAULT NULL, c INT NULL DEFAULT NULL ); CREATE TABLE bar ( a INT NULL DEFAULT NULL, b INT NULL DEFAULT NULL, c INT NULL DEFAULT NULL ); INSERT INTO foo (a, b) VALUES (1, 2); 

I need a trigger whose actions occur after the update only if the value is changed by the update. Thus, this UPDATE will not result in an INSERT:

 UPDATE foo SET a = 1 WHERE b = 2; 

But this UPDATE will result in an INSERT:

 UPDATE foo SET a = 2 WHERE b = 2; 

I wrote this trigger to handle this, however I hope the IF statement can be simplified.

 DELIMITER /// CREATE TRIGGER t_after_update_foo AFTER UPDATE ON foo FOR EACH ROW BEGIN IF ((OLD.a <> NEW.a OR OLD.a IS NULL OR NEW.a IS NULL) AND (NEW.a IS NOT NULL OR OLD.a IS NOT NULL)) OR ((OLD.b <> NEW.b OR OLD.b IS NULL OR NEW.b IS NULL) AND (NEW.b IS NOT NULL OR OLD.b IS NOT NULL)) OR ((OLD.c <> NEW.c OR OLD.c IS NULL OR NEW.c IS NULL) AND (NEW.c IS NOT NULL OR OLD.c IS NOT NULL)) THEN INSERT INTO bar (a, b, c) VALUES (NEW.a, NEW.b, NEW.c); END IF; END; /// DELIMITER ; 

Can I simplify IF statement? Or is there an easier general solution?

Answer 1

You can use coalesce() , which returns the first of its arguments, which is not null .

 if coalesce(old.a,'') <> coalesce(new.a,'') or coalesce(old.b,'') <> coalesce(new.b,'') or coalesce(old.c,'') <> coalesce(new.c,'') then insert ...; end if; 

It can be difficult to choose the second argument. The above example works for the general case when a, b and c are strings and when an empty string value is equivalent to null .

Answer 2

You can do this by comparing using the NULL-safe equality operator <=> and then negating the result using NOT .

So,

 ((OLD.a <> NEW.a OR OLD.a IS NULL OR NEW.a IS NULL) AND (NEW.a IS NOT NULL OR OLD.a IS NOT NULL)) 

will become

 !(OLD.a <=> NEW.a) 

To check if any of several columns has changed, you can do

 !(OLD.a <=> NEW.a AND OLD.b <=> NEW.b)