Q

What is the best way to get to connection information inside a trigger?

What is the best way to get to connection information inside a trigger? We log on to our app with a specific username and to SQL Server using a global username. Inside triggers, we want to update a "last_modified_by" column with the specific username. The only way I can see of doing this is to have a table with suser_sid, specific_user_name and date/time. I would insert a row at logon and then select that row from inside the trigger. Do you know of a better way?

What is the best way to get to connection information inside a trigger? We log on to our app with a specific username and to SQL Server using a global username. Inside triggers, we want to update a "last_modified_by" column with the specific username. The only way I can see of doing this is to have a table with suser_sid, specific_user_name and date/time. I would insert a row at logon and then select that row from inside the trigger. Do you know of a better way?
I don't think there are very many solutions for you that will be quick, easy and painless. The first thought that comes to mind is to use the SET CONTEXT_INFO command to set the specific username for each user that logs in for the duration of their session. You can then query the context_info column of sysprocesses from your trigger to record any changes to your database the specific user makes. I hope this helps!
This was first published in August 2005

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close