Get caller of trigger in SQL Server

Question

Can you advice me how I can retrieve caller ID or Name which executed a trigger?

For example I want to know which SP executes a trigger or maybe trigger called by updates from management studio?

I know that @@PROCID returns ID of trigger and can't be used.

Also I know solution when in SP we write CONTEXT_INFO and read it from trigger. But in this case we should SET CONTEXT_INFO in all SPs that modifies some table.

Is there exists some simplest way like @@PROCID ?

Answer 1

If it's SQL Server 2005 or 2008, and a DDL trigger, you can use eventdata().

Here's a link to the msdn page.

Basically it returns an XML dataset that you can parse to get things like who called it (data(/EVENT_INSTANCE/LoginName)[1]), what the command was, etc.