Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-11 : 08:37:13
|
| Nalini writes "I want to do audit trail in my application which connects to SQL Server 2000 backend. There are close to 15,000 users for this site. I use SQL server Authentication (login information that was assigned to me by the system administrator) to connect to the backend from my VB.NET application.I am thinking for using insert, update and delete triggers for tracking changes done to all the tables. I am going to log the history of changes done in another table.MY PROBLEM IS - I also need to maintain which user has made the change (This is the user ID used to log into the application, not sql server login). To summarize I need to set 3 additional fields USER ID, PROJECT ID and ROLE IN PROJECT, while tracking the history of changes.My purpose if to fill the following column in the history table1) MODIFIEDBY_USerID 2) CURRENT_PROJECT 3) PROJECT_ROLE 4) TABLE_NAME5) COLUMN_NAME6) OLD_VALUE7) NEW_VALUE8) DATE_OF_CHANGEColumn 1-3 are available in session variables and these columns are not available in all tables. The code base for this project is huge and I cannot make changes in code as it will be too much time consuming. Can you give me ideas as to how this can be done with minimal code changes?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-11 : 08:41:56
|
| There's no way to pass these values without changing code. Moreover you cannot pass variables to a trigger, so the changes would have to be done in the application or stored procedures that perform the updates.One method that might assist you is to use SET CONTEXT_INFO. This allows you to set specific information for the current session/connection, and it can be retrieved from within the trigger with a simple query. You'd have to change both the app code and trigger code to support it, but it's possible. I'd suggest looking in Books Online for more details to see if this is an option for you. |
 |
|
|
|
|
|
Audit Trail - Additional parameters to triggers