August 4, 2005
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.
