Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update trigger not firing if initial value is null

Posted on 2008-06-20
3
Medium Priority
?
502 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 1000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

971 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