Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create MS SQL 2005 Changes Log

Posted on 2009-05-04
5
Medium Priority
?
230 Views
Last Modified: 2012-05-06
I wish to create a changes log for many of my tables in a MS SQL 2005 DB.

I will shortly be allowing our 26 shops access to our main database.  They will be able to edit data.  Because of this I need a simple log of what they change!  I would like to record to a single table the following data:
Table Name, ID of Record Changed, Field Changed, Old Value, New Value, Date/Time, User.

I will then display this information to Managers & Supervisors in a simple Access DB form so we can see what they are actually doing.

Has anyone got an example of the code I require.  I'm a relative beginer with SQL so a simple Copy/Paste solution would be great!

I've seen various ways of doing this type of thing but could really do with
0
Comment
Question by:CABHugh
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24302127
well, you can create audit trail trigger. small examples are here.

http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24302133
0
 

Author Comment

by:CABHugh
ID: 24302376
Hi

Thanks for these.  I 've just had a look and am going to test http://www.codeproject.com/KB/database/AuditTrailGenerator.aspx first.  I know that this will create plenty of bloat but it really does give me a simple solution to restore data as I choose (if required!).

I'll let you know how I get on.

Regards
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24302382
sure, will wait for your update.
0
 

Author Closing Comment

by:CABHugh
ID: 31577907
Links provide that gave me the solution I needed.  Thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question