Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

Change the if condition to:

if (new.col1 != old.col1) or ( new.col1 is not null and old.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.

share|improve this answer
    
i mean in original code we are just checking that if old value is different than a new value , then trigger should execute the update part..why does it makes so much difference . –  user21546 May 6 '13 at 9:46
    
I know - you need the extra condition in my answer because things like "old.col1 = new.col1" don't evaluate as you'd like when NULL values are involved –  Phil May 6 '13 at 10:07
    
ok !! well your code is also working and my modified code too !! –  user21546 May 6 '13 at 10:36
    
The null-safe comparison operator (<=>) might be a better choice. –  Brilliand Jan 15 at 16:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.