Solved

Update trigger not firing if initial value is null

Posted on 2008-06-20
3
492 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now