1

I am googling it since a while but unfortunately no clear solution found. The question could be separated to EF specific part and not EF specific part I know.

I have an web application (ASP.NET MVC) which uses SQL Server via EF 6. I would like to implement audit triggers, so I would like to be available some audit information to the trigger, like web logged in current user. I thought there should be some connection level parametrizing facility in SQL Server to initialize custom SET variables what can be accessed during that connection via SQL (in the triggers. The only thing I found is the way restricted CONTEXT_INFO() which is binary.

I've also know temp tables what are connection local, but it seems to be a bit overkill to start all connection to create a temp table and insert a row.

However my biggest concern is connection pooling. It seems to me that a proposed solution either does not work correctly because of connection pooling (temp tables), either it effectively kills connection pooling. (connection string manipulation techniques)

Still I think this audit trigger, which must know web user name is so common and basic so I am hoping someone figured out the correct solution and it is available, I just can not find it.

trailmax
  • 34,305
  • 22
  • 140
  • 234
g.pickardou
  • 32,346
  • 36
  • 123
  • 268

1 Answers1

2

If you are using EF, why bother with triggers? You can override DbContext.SaveChanges() and implement your audit logic in C# with all the required information available at hand. See this answer for example of a basic audit. Or here for more comprehensive audit.

Community
  • 1
  • 1
trailmax
  • 34,305
  • 22
  • 140
  • 234
  • Because I have many SQL trigger (or more: trigger generator T4 templates) in hand. Also there are many out of the box trigger based audit solutions out there what are stabilized. I do not want to reinvent the wheel, and reimplement all of those in C#. I just want to log my ASP.NET user and not the SQL Server logged in user. – g.pickardou Jul 13 '15 at 08:16
  • Did some research and I am convinced. Thanks. – g.pickardou Jul 13 '15 at 09:58
  • Good shout. Any particular argument helped to make your mind? – trailmax Jul 13 '15 at 10:18
  • 1
    Yes, I found some pretty good code / nuget packages with unit test then I can implement and test my own version with relative low cost. Performance will be degraded of course compared to the real trigger versions because of extra roundtrip(s!) and some functions are limited for example I can not log transaction id, to associate audit records what were atomic, but still seems to be a good trade via elimination of TSQL (I mean no need to multiple platforms to maintain and deploy) – g.pickardou Jul 13 '15 at 10:43