SQL Server : get at the name of the app causing an update in a trigger

Question

We're trying to pinpoint the source of some unexpected updates happening on a SQL Server table. What I'd like to do is create a trigger on that table monitoring that column we're interested in, and when updates occur, write some audit info into a separate table.

Works great and fine for things like user name, date, old and new columns values (from the Inserted and Deleted trigger tables) - but I'd like more :-)

In SQL Profiler, you can see the name of the app that's connected to SQL Server in the profiles - the part that can be defined in the connection string as Application Name:

Data Source=(local);Initial Catalog=AdventureWorks;
Integrated Security=True;Application Name="My Application"

Is there any way in a T-SQL FOR UPDATE trigger to get at this information?

Answer 1

SELECT APP_NAME()

Answer 2

Marc_S the sP_whoIsActive stored Procedure probably will solve Your problems as posted here

and it's awesome part of T-SQL by the way