Getting session value in SQL Server

[usamaalam]

Hello everybody,

I have a SQL Server 2005 database and I have created a table to maintain audit trail. I am planning to create trigger with table that need to be logged, but I also need current user's ID that is in session to insert in the audit trail table so that I can save who did what. What is the best way to transfer the session variable in the trigger? Is there a way we can directly get it at the database level or we need to provide it as parameter?

Thanks.

[mendhak]

How is the session ID being sent to the database currently?

[usamaalam]

Not sending it right now, just planning on how to do it.

Thanks,

[mendhak]

In order for it to be used in the trigger, it'll need to go into the table. So the simplest way is to have a column in that table for the session value so the trigger can see it.

[usamaalam]

Yes, I'll be having one column in the audit trail table that will store the ID of the user from session, but the question is how to pass the value of the session from application to trigger. Is there a way we can read the session value in sql server or I need to send the session variable for each database update that need to be logged?

Thanks.

[mendhak]

OK, thing about how triggers work. A trigger occurs after something happens to a row in a table. The trigger can then take the information from the modified row and do something with it. This implies that the information needs to be in the table. The trigger doesn't know of any .NET applications. All it knows is that a row has changed. Therefore, you need to pass the sessionid information to the table itself and get the trigger to read that column.

You cannot read session values from sql server because it is application-agnostic. Your application needs to pass the session value to the stored procedure or to the UPDATE/INSERT statement. It's just like any other variable (such as a string variable) that you'd pass to a stored procedure.