Monday, June 26, 2006
Suggestion: SQL Connection Scope Properties
The easiest and most common way of keeping a audit trail on tables in SQL server is thru a trigger - the trigger records what the old and new values. Simple fast, efficient. The problem is that currently with web app's there is no way to log who the user that did the change is ,because SQL can only detect who the current windows or SQL user is not who the user is as identified by your webapp login system. So triggers have gotten to be "not so useful" instead we have either had to fall back to manual explicit logging calls to the logging tables (which is error prone) or some have resorted to adding a lastchanged user filed to the database that the trigger would use. I think it would be a good idea if we could allow a property (Either a specific username or perhaps even a keyvalue pair type of thing) to e passed on the connection string and available as a function in SQL statements thruout that connection. This may cause problems with connection pooling ???