By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

MS SQL 8, track changes?

Plater
Expert 5K+
P: 7,872
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.
Dec 10 '08 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Dec 12 '08 #2

Plater
Expert 5K+
P: 7,872
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
Dec 12 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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!
Dec 12 '08 #4

Plater
Expert 5K+
P: 7,872
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

Expand|Select|Wrap|Line Numbers
  1. -- Insert statements for trigger here
  2. DECLARE @WhatType as varchar(20)
  3. DECLARE @WhoDidIt as varchar(100)
  4. DECLARE @Notes as varchar(500)
  5. DECLARE @Check1 as int
  6. DECLARE @Check2 as int
  7.  
  8. SET @WhoDidIt = system_user
  9. SET @Notes='Testing'
  10.  
  11. SELECT @Check1 = (SELECT COUNT(*) FROM Inserted)
  12. SELECT @Check2 = (SELECT COUNT(*) FROM Deleted)
  13.  
  14. if(@Check1>0 AND @Check2>0)
  15. BEGIN
  16.     --isUpdate
  17.     Set @WhatType='Update'
  18.     SELECT @Notes='Delete: '+(SELECT SomeColumn FROM Deleted)
  19.     Set @Notes=@Notes+ 'Insert: '+(SELECT SomeColumn FROM Inserted)
  20. END
  21. ELSE
  22.     if(@Check1>0 AND @Check2=0)
  23.     BEGIN
  24.         --isInsert
  25.         Set @WhatType='Insert'
  26.         SELECT @Notes='Insert: '+(SELECT SomeColumn FROM Inserted)
  27.     END
  28.     ELSE
  29.         if(@Check1=0 AND @Check2>0)
  30.         BEGIN
  31.             --isDelete
  32.             Set @WhatType='Delete'
  33.             SELECT @Notes='Delete: '+(SELECT SomeColumn FROM Deleted)
  34.         END
  35.         ELSE
  36.             BEGIN
  37.                 Set @WhatType='Unknown'
  38.             END
  39.  
  40. INSERT INTO [ChangesTracking]
  41. (
  42.     [Occured]
  43.    ,[WhoDidIt]
  44.    ,[TableAffected]
  45.    ,[Type]
  46.    ,[Notes]
  47. )
  48. VALUES
  49.  (
  50.     getdate()
  51.    ,@WhoDidIt
  52.    ,'MyTableName'
  53.    ,@WhatType
  54.    ,@Notes
  55. )
  56.  
Dec 15 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Don't forget error handling and if you can use transaction, use it.

-- CK
Dec 15 '08 #6

ck9663
Expert 2.5K+
P: 2,878
Here's more about TRANSACTIONS.

-- CK
Dec 15 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.