CODEWITHSUNDEEP
c#sql-serverasp.net-mvc-3entity-frameworklogging
Goldentp
Goldentp

Reputation: 197

How can I keep track of changes to a Database?

I am creating a website that will be used by an accounting dept. to track budget expenditures by different projects.

I am using SQL Server 2008 R2 for the database and ASP.net C# MVC 3 for the website.

What my boss has asked me to do is every time any user updates or creates a project, we need to log that change into a new table called Mapping_log. It should record the whole Mapping row being saved or created, and additionally the user and the datestamp. The notes field will now be mandatory, and the note should be saved to the Mapping_log.

Now when editing the PA, the Notes field will always be empty and below it, it should have a list of the older notes organized by date. I have been looking into maybe using Nlog and Log4net but I have not been able to find any good tutorials for a situation like mine. It seems that those modules are mostly used for error logging, which although important is not exactly what I am try to do at the moment.

I need some direction... does anyone have any advice or tutorials that I could use to learn how I can implement a process that will keep track of changes made to the data by users of the site.

Thanks for your help/advice!

Upvotes: 1

Views: 3455

Answers (2)

marc_s
marc_s

Reputation: 755287

I would just create two triggers - one for the update, one for the insert.

These triggers would look something like this - assuming you also want to log the operation (insert vs. update) in your Mapping_Log table:

CREATE TRIGGER trg_Mapping_Insert
ON dbo.Mapping
AFTER INSERT
AS
   INSERT INTO dbo.Mapping_Log(col1, col2, ..., colN, User, DateStamp, Operation)
      SELECT 
         col1, col2, ..., colN, SUSER_NAME(), GETDATE(), 'INSERT'
      FROM
         Inserted

(your UPDATE trigger would be very similar - just replace "insert" by "update" wherever it appears)

This is done "behind the scenes" for you - once in place, you don't have to do anything anymore to have these operations "logged" to your Mapping_Log table.

Upvotes: 1

Chopin
Chopin

Reputation: 1452

You can consider two new features that SQL Server 2008 introduced: Change Tracking and Change Data Capture.

You could use that and avoid your custom Mapping_log table.

But if you need to apply a more complex -business- rule, perhaps it will better doing that in the application layer, rather than purely in the database.

Regards.

Upvotes: 3

Related Questions