views:

448

answers:

2

I would like to do both of the following things:

  1. use audit triggers on my database tables to identify which user updated what;
  2. use connection pooling to improve performance

For #1, I use 'system_user' in the database trigger to identify the user making the change, but this prevent me from doing #2 which requires a generic connection string.

Is there a way that I can get the best of both of these worlds?

ASP.NET/SQL Server 2005

+1  A: 

Unfortunately, no. Identifying the user just from the database connection AND sharing database connections between users are mutually exclusive.

Rory
+1  A: 

Store the user from your web application in the database and let your triggers go off that stored data. It might even be better to let the web app handle writing all logging information to the database.

Chris Westbrook
Unfortunately, there is no easy way to know from the trigger which "user" is making the changes. You need auxiliary fields in the record to tie it to the account, and you have to pull crazy tricks to track users for DELETEs.
Euro Micelli