MS SQL 8, track changes?

[Plater]

As a quick setup:
My company uses MS SQL SERVER 8 as a backend, and uses an old mdb/mde file as a frontend with linked tables.

Lately we've been running into a problem where a particular entry in a table is getting deleted. And nobody knows why. The table does not get additions or subtractions to it. Its used as shipping choices for another table.
Like:
1 Priority
2 NextDay
3 OverNight
(etc)

And the exact same entry keeps getting removed. We have been unable to find out where/why its happening.

I was wondering if there was a way to configure the SQL server to track anything it could about changes made to that table?
Such as a snapshot of the user instance like its displayed in the "Activity Monitor" (SQL Server Management Studio)?
Really I think a datetime and the computer/user of the connection that causes the change would be good.

Any ideas? I cannot find any reporting system to use for this.

[ck9663]

If the table is in SQL Server, you can create a trigger to get all the information (ie system_user(), date and time) when someone delete it.

-- CK

[Plater]

I found the triggers after some digging, am unfamiliar with system_user(), I hope it does what I think it does.
thanks!


edit: its system_user, not system_user() it would seem

[ck9663]

Yes. It's a function that returns the current user name and is very helpful specially if your server is configured to accept Mix Authentication. What you do is to create a trigger that insert to a "tracking" table the date, tmie and the user name who did the change. Since the user does not know there's a trigger on the table, you can actually monitor the movement of your data.whether INSERT, UPDATE or DELETE.

Happy coding!

[Plater]

Yes indeed, here's what I ended up going with.

There is probably a more effecient way to do this, but the hope is that it never gets triggered

-- Insert statements for trigger here
DECLARE @WhatType as varchar(20)
DECLARE @WhoDidIt as varchar(100)
DECLARE @Notes as varchar(500)
DECLARE @Check1 as int
DECLARE @Check2 as int
 
SET @WhoDidIt = system_user
SET @Notes='Testing'
 
SELECT @Check1 = (SELECT COUNT(*) FROM Inserted)
SELECT @Check2 = (SELECT COUNT(*) FROM Deleted)
 
if(@Check1>0 AND @Check2>0)
BEGIN
    --isUpdate
    Set @WhatType='Update'
    SELECT @Notes='Delete: '+(SELECT SomeColumn FROM Deleted)
    Set @Notes=@Notes+ 'Insert: '+(SELECT SomeColumn FROM Inserted)
END
ELSE
    if(@Check1>0 AND @Check2=0)
    BEGIN
        --isInsert
        Set @WhatType='Insert'
        SELECT @Notes='Insert: '+(SELECT SomeColumn FROM Inserted)
    END
    ELSE
        if(@Check1=0 AND @Check2>0)
        BEGIN
            --isDelete
            Set @WhatType='Delete'
            SELECT @Notes='Delete: '+(SELECT SomeColumn FROM Deleted)
        END
        ELSE
            BEGIN
                Set @WhatType='Unknown'
            END
 
INSERT INTO [ChangesTracking]
(
    [Occured]
   ,[WhoDidIt]
   ,[TableAffected]
   ,[Type]
   ,[Notes]
)
VALUES
 (
    getdate()
   ,@WhoDidIt
   ,'MyTableName'
   ,@WhatType
   ,@Notes
)
 

[ck9663]

Don't forget error handling and if you can use transaction, use it.

-- CK

[ck9663]

Here's more about TRANSACTIONS.

-- CK