Automatically log changes made to records in database

I'm wondering what the best way to implement this would be.

Basically 开发者_运维问答our project has a requirement that any change made to records in the database should be logged. I already have it completed in C# using Reflection and Generics but I'm 100% sure that I used the best method.

Is there a way to do this from inside the SQL database?

The big key is that the way our project works, the ObjectContext is disconnected, so we couldn't use the built in Change Tracking and had to do our own compares against previous Log items.

---

If you're using SQL Server 2008 or higher, you can implement either change tracking or change data capture directly on the database. Note that the latter is only available in the Enterprise edition engine. There are pros and cons to each method. You'll have to review each solution for yourself as there isn't enough requirement information to go on in the question.

If you're using SQL Server 2005 or below, you'll have to resort to a trigger-based solution, as suggested by the other answers.

---

You want to look at database triggers.

---

depending on the complexity of your datamodel you could setup on update/insert/delete triggers on the relevant tables - these triggers could log whatever is needed (old/new values, User, timestamp etc.)... see http://msdn.microsoft.com/de-de/library/ms189799.aspx

---

Look at my blog to see how you can track data changes without database scheme modification: part1,part2

---

For your project requirement, SQL trigger is the better solution than the current C# reflection. Becaz triggers provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. More over, the requirement is full filled at DataBase layer itself and so hosted as the single solution for various front end applications.