Question: I have a database with multiple tables and I want to log the users activity via my MVC 3 web application. 

```
User X updated category HELLO. Name changed from 'HELLO' to 'Hi There' on 24/04/2011       
User Y deleted vehicle Test on 24/04/2011.
User Z updated vehicle Bla. Name changed from 'Blu' to 'Bla' on 24/04/2011.
User Z updated vehicle Bla. Wheels changed from 'WheelsX' to 'WheelsY' on 24/04/2011.
User Z updated vehicle Bla. BuildProgress changed from '20' to '50' on 24/04/2011

```

My initial idea is to have on all of my actions that have database crud, to add a couple lines of code that would enter those strings in a table.

Is there a better way of checking which table and column has been modified than to check every column one by one with if statements (first I select the current values, then check each of them with the value of the textbox) I did that for another ASPX web app and it was painful.

Now that I'm using MVC and ADO.NET Entity Data Model I'm wondering if a faster way to find the columns that were changed and build a log like the one above.

Answer: 
You can also accomplish this by putting your database into full recovery mode and then reading the transaction log.

When database is in a full recovery mode then sql server logs all Update, insert and delete (and others such as create, alter, drop..) statements into it's transaction log.

So, using this approach you dont need to make any additinal changes to your application or your database structure. 

But you will need 3rd party [sql transaction log reader](http://www.apexsql.com/sql_tools_log.aspx). Red gate has a free solution for sql server 2000 only. If your server is 2005 or higher you would probably want to go with [ApexSQL Log](http://www.apexsql.com/sql_tools_log.aspx)

Also, this approach will not be able to audit select statements but it's definately the easiest to implement if you dont really need to audit select queries.
You could have a trigger (AFTER insert/update/deelte) on each table you want to monitor. The beauty is columns\_updated() which returns a barbinary value, indicating which columns have been updated.

Here is some snippet of code that I put in each trigger:

```

IF (@@ROWCOUNT = 0) return

declare @AuditType_ID  int ,
        @AuditDate     datetime ,
        @AuditUserName varchar(128),
        @AuditBitMask  varbinary(10)

select  @AuditDate     = getdate() ,
        @AuditUserNAme = system_user,
        @AuditBitMask  = columns_updated()

-- Determine modification type
IF (exists (select 1 from inserted) and exists (select 1 from deleted))
  select @AuditType_ID = 2 -- UPDATE
ELSE IF (exists (select * from inserted))
  select @AuditType_ID = 1 -- INSERT
ELSE
  select @AuditType_ID = 3 -- DELETE

(record this data to your table of choice)

```

I have a special function that can decode the bitmask values, but for some reason it is not pasting well here. Message me and I'll email it to you.