Update trigger not firing if initial value is null
Hi. I have an update trigger that does not fire initially, using MS SQL 2000.
If old_value <> new_value then....
If the old_value is <Null>, it does not fire.
If I enter in a number, and enter in a different number, it does fire.
What am I missing? Thanks.
If old_value <> new_value then....
If the old_value is <Null>, it does not fire.
If I enter in a number, and enter in a different number, it does fire.
What am I missing? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From BOL:
A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
Therefore use:
If (old_value <> new_value) or old_value is null
or, for completeness:
If (old_value <> new_value) or old_value is null or new_value is null
A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
Therefore use:
If (old_value <> new_value) or old_value is null
or, for completeness:
If (old_value <> new_value) or old_value is null or new_value is null
ASKER