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

[code=sql]
-- 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='Upda te'
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='Inse rt'
SELECT @Notes='Insert: '+(SELECT SomeColumn FROM Inserted)
END
ELSE
if(@Check1=0 AND @Check2>0)
BEGIN
--isDelete
Set @WhatType='Dele te'
SELECT @Notes='Delete: '+(SELECT SomeColumn FROM Deleted)
END
ELSE
BEGIN
Set @WhatType='Unkn own'
END

INSERT INTO [ChangesTracking]
(
[Occured]
,[WhoDidIt]
,[TableAffected]
,[Type]
,[Notes]
)
VALUES
(
getdate()
,@WhoDidIt
,'MyTableName'
,@WhatType
,@Notes
)
[/code]

[ck9663]

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

-- CK

[ck9663]

Here's more about TRANSACTIONS.

-- CK