Solved

Update trigger not firing if initial value is null

Posted on 2008-06-20
3
495 Views
Last Modified: 2010-04-21
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.
0
Comment
Question by:D4430
3 Comments
 
LVL 2

Accepted Solution

by:
balochdude earned 250 total points
ID: 21832243
just put a check if old_value is null then old_value = 1 (or some more appropriate value).

for example, i am doing the following in one our triggers::
       IF @max_line_number IS NULL
      BEGIN
            SET @max_line_number = 1000
      END
      ELSE
      BEGIN
            SET @max_line_number = @max_line_number + 1
      END
0
 

Author Closing Comment

by:D4430
ID: 31469202
Thanks.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21832399
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

0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question