datetime('now', 'localtime') is always a text value.
NULL compared to anything is always NULL.
A NULL condition is false.
`WHEN OLD.Modified is NULL OR OLD.Modified != DATETIME('NOW','localtime')`
is just a long drawn out way of saying
`WHEN OLD.Modified IS NOT DATETIME('NOW', 'localtime)`