Solved

Update trigger not firing if initial value is null

Posted on 2008-06-20
3
498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

626 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