How to properly implement "per field history" through triggers in SQL Server (2008)

Question

Hi, so, I'm facing the challenge of having to log the data being changed for each field in a table. Now I can obviously do that with triggers (which I've never used before, but I can imagine is not that difficult), but I also need to be able to link the log who performed the change which is where the problem lies. The trigger wouldn't be aware of who is performing the change and I can't pass in a user id either.

So, how can I do what I need to do? If it helps say I have these tables:

Employees {
    EmployeeId
}

Jobs {
    JobId
}

Cookies {
    CookieId
    EmployeeId -> Employees.EmployeeId
}

So, as you can see I have a Cookies table which the application uses to verify sessions, and I can infer the user out of it, but again, I can't make the trigger be aware of it if I want to make changes to the Jobs table.

Help would be highly appreciated!

Answer 1

We use context_info to set the user making the calls to the DB. Then our application level security can be enforced all the way to in DB code. It might seem like an overhead, but really there is no performance issue for us.

make_db_call() {
   Set context_info --some data representing the user----
   do sql incantation
}

in db

  select @user = dbo.ParseContextInfo()
  ... audit/log/security etc can determine who....

To get the previous value inside the trigger you select from the 'deleted' pseudo table, and to the get the values you are putting in you select from th 'inserted' pseudo table.

Before you issue linq2sql query issue the command like this.

context.ExecuteQuery('exec some_sp_to_set_context ' + userId')

Or more preferably I'd suggest an overloaded DataContext, where the above is executed before eqch query. See here for an example.

we don't use multiple SQL logins as we rely on the connection pooling and also locking down the db caller to a limited user.