This site will look better in a browser that supports Web standards, but it is accessible to any browser or Internet device.

August 4, 2005

1993
2001
2002
2003
2004
2005
January
February
March
April
May
June
July
August
Su M Tu W Th F Sa
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
September
October
November
December
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018

"Gotchas" With Triggers and NULLs

Well, the program I wrote about Tuesday is still plunking away in defiance of my plan to figure out what's wrong with it. So I decided to cause myself a new problem.

The devices that send us data require us to acknowledge that we got the information. Otherwise it just keeps sending the same thing over and over. This is a problem if we're actually getting the data but not replying fast enough, because we're going to wind up with a bunch of duplicate records in the database.

So I wrote a trigger (I'll spare you the irrelevant stuff).


CREATE TRIGGER RemoveDuplicates ON ReportTable BEFORE INSERT
AS
-- Variable declarations here
SET @LastReportsTime = (SELECT TOP 1 ReportTime
FROM ReportTable WHERE DeviceID = @ThisDeviceID
ORDER BY ReportTime DESC)
IF @ThisReportsTime <> @LastReportsTime
-- Allow the INSERT to continue
GO

Worked like a charm.

Until we activated a new device. Acknowledgments were getting sent but nothing was going into the database. I checked my flaky data receiver and it was still going strong in spite of the fact that I wrote it.

See where I screwed up?

When there are no previous records, @LastReportsTime isn't assigned, so it stays NULL. And comparing NULL to anything, including itself, returns NULL which means the expression will never be true. Every report that came in was being rejected.

Luckily we were only bench-testing the new unit, so the lost data wasn't important. Of course, the fix was to change the comparison to IF @LastReportsTime IS NULL OR @ThisReportsTime <> @LastReportsTime and everything worked fine thereafter.

Remember, just because some languages let you get away with playing fast-and-loose on object typing doesn't mean they all will.

This page's URL is http://jasonfleshman.org

This page last updated Oct 6, 2014 8:34:36 AM.