SQLite "modified" trigger which only only updates modified field if values have actually changed

Question

I'm working on a trigger for SQLite which will update the "modified" field if & only if the data in all the columns under consideration has actually changed.

I've gotten very close with:

CREATE TRIGGER "main"."Orders_modified"
BEFORE UPDATE OF "order_date", "grand_total_in_dollars", "ship_to", "store", "more_href", "order_id", "grand_total" ON "Orders"
BEGIN
  UPDATE Orders 
  SET modified=(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW', 'localtime')) 
  WHERE (OLD.order_id=NEW.order_id) 
    AND (
          (OLD.order_date != NEW.order_date) OR 
          (OLD.grand_total_in_dollars != NEW.grand_total_in_dollars) OR
          (OLD.ship_to != NEW.ship_to) OR 
          (OLD.store != NEW.store) OR 
          (OLD.more_href != NEW.more_href) OR 
          (OLD.grand_total != NEW.grand_total)
    );
END;

The problem is, it does not work when a value is being put into any of those fields for the first time; i.e., when the old value is 'Null'. Works great otherwise.

My temporary workaround to this is to just default all the monitored fields as empty strings, but I'd like to understand why (& if) this is necessary.

Any tips as to why this may be, & any workarounds that might be available?

Many thanks in advance.

Answer 1

Instead of the operator != use the operator IS NOT which works when one or both of the operands are NULL:

......................................................
WHERE (OLD.order_id=NEW.order_id) 
  AND (
       (OLD.order_date IS NOT NEW.order_date) OR 
       (OLD.grand_total_in_dollars IS NOT NEW.grand_total_in_dollars) OR 
       (OLD.ship_to IS NOT NEW.ship_to) OR 
       (OLD.store IS NOT NEW.store) OR 
       (OLD.more_href IS NOT NEW.more_href) OR
       (OLD.grand_total IS NOT NEW.grand_total)
      );