MSSQL creating changelog for table update

I have software that pulls registry information on software installed on a system, and writes that data into an MS SQL 2000 database. This is, of course a very large database with thousands of systems, and most having between 100 and 400 registry entries. I'm trying to create a table that will log any changes made to the table. I know it must be possible with an UPDATE trigger to compare the deleted and inserted tables. Unfortunately, I don't know much on how triggers work, and don't want to have to compare every entry back and forth to find which ones are new, and which ones are removed.

Does anyone have any hints on how to go about finding what entries are in one table, and not the other?

If you can use third party auditing tools, take look at Lumigent's Entegra.

You can have 3 separate triggers, 1 for update, 1 for insert, and 1 for delete.

Okay, I've made a little progress into this, but have hit a wall. I'll give some details on the database structure.

The software being used scans the registry of a computer and sends certain information like hardware and software to a database. My goal is to track whenever a computer is scanned, if anything has been added or removed and make note in a separate table. Now, I did not write this software, or design the database, I'm just trying to work with this. Basically, this is how it works.

Software scans the computer.
Software opens a session with the mssql server.
Software deletes all entries for that pc name.
Software inserts the new entries in the table.

Unfortunately, each reg key is it's own line. And, there is no unique id field (I've had words with the developer about this.) Anyways, this is the solution I've come up with (although surely not the best.)

Delete trigger to write all deleted rows to a new table (one with a unique ID, as well as a timestamp and field noting removed.) This was pretty easy.

Insert trigger
Create temp table with inserted data (because I can't modify the generated inserted table)
Create temp2 table with the table from the table I wrote the deleted data to. (This is the step that is throwing me off.)
Scroll through each row in the inserted temp table.
Compare to a select statement in the deleted temp table
If there's a match, delete the removed row.
If there's no match, add the added info to the permanent table.

Here's the code, anyone able to help?:

CREATE TRIGGER hardadd ON [cli.Hardware]
FOR INSERT
AS

DECLARE @primaryKey BIGINT
DECLARE @domainName VARCHAR(50)
DECLARE @pcName VARCHAR(50)
DECLARE @devName VARCHAR(50)
DECLARE @devID SMALLINT
DECLARE @devValueType VARCHAR(50)
DECLARE @devValue VARCHAR (255)

-- This is the Temp table for the inserted data
CREATE TABLE #Temp (
primaryKey bigint IDENTITY (1, 1),
domainName varchar (50),
pcName varchar (50),
devName varchar (50),
devID smallint,
devValueType varchar (50),
devValue varchar (255)
)
-- This is the Temp table for the previously deleted data
CREATE TABLE #Temp2 (
primaryKey bigint,
domainName varchar (50),
pcName varchar (50),
devName varchar (50),
devID smallint,
devValueType varchar (50),
devValue varchar (255),
infoTimeStamp varchar (50),
addremove varchar (10)
)

-- Copy Inserted data into Temp table
INSERT INTO #Temp (domainName, pcName, devName, devID, devValueType, devValue) SELECT * FROM INSERTED

-- Set domain name and pc name, these are all the same for the trigger
SELECT @domainName = (SELECT TOP 1 domainName FROM #Temp)
SELECT @pcName = (SELECT TOP 1 pcName FROM #Temp)

-- Insert data from the Tracking table into the Temp2 table (BROKEN)
INSERT INTO #Temp2
(primaryKey, domainName, pcName, devName, devID, devValueType, devValue, infoTimeStamp, addremove)
SELECT * FROM [cli.HardTracking]
WHERE (pcName = @pcName) AND (infoTimeStamp = GetDate()) AND (addremove = 'Removed')

-- WHILE loop to scroll through rows of Temp table
WHILE EXISTS (SELECT * FROM #Temp) BEGIN

-- Set variables from current rows of Temp table
SELECT @primaryKey = (SELECT TOP 1 primaryKey FROM #Temp)
SELECT @devName = (SELECT TOP 1 devName FROM #Temp)
SELECT @devID = (SELECT TOP 1 devID FROM #Temp)
SELECT @devValueType = (SELECT TOP 1 devValueType FROM #Temp)
SELECT @devValue = (SELECT TOP 1 devValue FROM #Temp)

-- If there is matching data in Temp2 table....
IF EXISTS (SELECT * FROM #Temp2 WHERE (devName = @devName) AND (devID = @devID)
AND (devValueType = @devValueType) AND (devValue = @devValue))
BEGIN
-- Delete the matching entry
DELETE [cli.HardTracking] WHERE (devName = @devName) AND (devID = @devID)
AND (devValueType = @devValueType) AND (devValue = @devValue)
END

ELSE BEGIN
-- Otherwise, insert new info into the Tracking table
INSERT INTO [cli.HardTracking]
(domainName, pcName, devname, devID, devValueType, devValue, infoTimeStamp, addremove)
VALUES (@domainName, @pcName, @devname, @devID, @devvalueType, @devValue, GetDate(), 'Added')
END

-- Delete the Temp table row I just parsed
DELETE #Temp FROM (SELECT TOP 1 * FROM #Temp) AS t1 WHERE #Temp.primaryKey = t1.primaryKey

END

-- Drop tables
DROP TABLE #Temp
DROP TABLE #Temp2

GetDate() returns time to the millisecond so the data comparison in your "INSERT INTO #Temp2" query will never match up. As a result, the select query will always return 0 rows.

That was indeed my problem. I changed the code to "infoTimeStamp = convert(smalldatetime, GetDate())" and it worked. Thank you very much.

Alright, I found one other problem still getting me.

On the line:
IF EXISTS (SELECT * FROM #Temp2 WHERE (devName = @devName) AND (devID = @devID)
AND (devValueType = @devValueType) AND (devValue = @devValue))

There are some rows where devID and devValueType are NULL. When if checks to see if the value is NULL, it returns false, which is correct by the ANSI standards. I've tried setting SET ANSI_NULLS OFF, and it doesn't seem to work. Is there a different setting that I may need to set? I'm using MSSQL 2000.

In stored procedures and triggers, SQL Server uses the NSI_NULLS settings that were set at creation time. They can't be changed at run time for these. You should instead explicitly check for null values.

AND (devID = @devID Or devID is Null)
AND (devValueType = @devValueType Or devValueType is Null)

Awesome, thanks again.