5

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 ?

Improve this question
4
  • 1
    Are you looking for the name of the calling object or the name of the calling user/security principal? Commented Mar 30, 2011 at 12:17
  • Not really. It is possible to get the TSQL call stack using extended events but this is a heavyweight thing not suitable for general use in a trigger. Commented Mar 30, 2011 at 12:36
  • @Quantum Elf: I looking for name of the calling object or it ID Commented Mar 30, 2011 at 14:43
  • @Martin: Yes, Looks like I will take technique with CONTEXT_INFO Commented Mar 30, 2011 at 14:43

1 Answer 1

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Unfortunately eventdata() not suitable because my trigger is DML
@Anton - then as far as I am aware, you can't get what you are after.
Anyway thanks for reply, now I know about eventdata() for future

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.