Results 1 to 9 of 9

Thread: MSSQL creating changelog for table update

  1. #1
    Join Date
    Jun 2005
    Posts
    5

    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?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    If you can use third party auditing tools, take look at Lumigent's Entegra.

  3. #3
    Join Date
    Feb 2003
    Posts
    1,048
    You can have 3 separate triggers, 1 for update, 1 for insert, and 1 for delete.

  4. #4
    Join Date
    Jun 2005
    Posts
    5
    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

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    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.

  6. #6
    Join Date
    Jun 2005
    Posts
    5
    That was indeed my problem. I changed the code to "infoTimeStamp = convert(smalldatetime, GetDate())" and it worked. Thank you very much.

  7. #7
    Join Date
    Jun 2005
    Posts
    5
    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.

  8. #8
    Join Date
    Feb 2003
    Posts
    1,048
    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)

  9. #9
    Join Date
    Jun 2005
    Posts
    5
    Awesome, thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •