How to use system_user in audit trigger but still use connection pooling?

Question

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

Answer 1

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

Answer 2

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.