Trigger doesn't run when value of a column changes from null to 1

I have this trigger:

delimiter $

create trigger tr 
after update on t1
for each row
begin

  if new.col1 !=old.col1 
    then update t2 set col2 =1 where t2.col3=t1.col3;
  end if;

end

$

This trigger works when col1's value changes from 0 to 1 but
when its changes from null to 1, the trigger doesn't make any changes in t2.

I just wanted to know why it is so. I know null is different, but I'm just comparing the values.

I have solved my problem by doing new.col1 =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)

StackExchange Database Administrators Q#41570, answer score: 8