Trigger doesn't run when value of a column changes from null to 1
Change the if condition to:
if (new.col1 != old.col1) or ( new.col1 is not null and old.col1 is null )
or ( old.col1 is not null and new.col1 is null )
That should solve your problem. The original code didn't work correctly as you can't test for equality with a NULL
value - you have to use IS NULL
or IS NOT NULL
.
MySQL has also a "null-safe equals" operator: <=>
which can be used here to make the above condition simpler (and still equivalent):
if not (new.col1 <=> old.col1)
IF !(NEW.col1 <=> OLD.col1)
THEN UPDATE t2 SET col2 = 1 WHERE t2.col3 = t1.col3;
END IF;
Explanation:
<=> is a null safe operator, from the MySQL manual:
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL
Beware though, that this trigger will fire also when col1
's value changes from 1 to NULL
, don't know if that is what you want.
You could rewrite the condition this way, if you don't want this behavior:
IF (!(NEW.col1 <=> OLD.col1) AND NEW.col1 IS NOT NULL)
THEN UPDATE t2 SET col2 = 1 WHERE t2.col3 = t1.col3;
END IF;