Solved

track database changes

Posted on 2010-11-24
4
324 Views
Last Modified: 2012-06-27
How can i track changes in an sql server 2005 database?

For example, if I wanted to know the inserts,deletes and edits(original and after) that occurred in a certain day- how can i do that?
0
Comment
Question by:NormanMaina
4 Comments
 
LVL 25

Accepted Solution

by:
Luis Pérez earned 125 total points
ID: 34203375
The easiest way: create a datetime field in the table with default value to GETDATE(). So in each record saved, you will have the date and time it has been saved.

But that doesn't work for deletes, of course, because the deleted record is no longer in the database. For that case a good option is to use a Trigger for delete and track the date and time and the unique ID of the deleted record in another table.

If you want to know more about triggers, check this:
http://www.codeproject.com/KB/database/TriggersSqlServer.aspx

Hope that helps.
0
 
LVL 17

Assisted Solution

by:sweetfa2
sweetfa2 earned 125 total points
ID: 34203377
create triggers to farm the data off to a data warehouse for the data that you are interested in.
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 125 total points
ID: 34203386
In SQL Server 2008 there is CDC which i have included a link for, but i see you are using SQL Server 2005.

http://msdn.microsoft.com/en-us/library/bb522489(v=SQL.100).aspx

With SQL Server 2005 you will have to create triggers to insert the data into an audit table.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 125 total points
ID: 34203971
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

825 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