Is it possible to get execution call stack in a trigger?

I have 10 stored procedures and each of them does INSERTs into one tableX.

Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ?

Thank you.

Yes, it is possible to identify the running code, by using the @@procid system function, and better OBJECT_NAME(@@PROCID) to have the complete name.

Definition: "Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider."

You can read about it here.

Another option would be to check the sql plan of the current spid and save that info in a logging table.
A sample query to be used in each procedure to save audit data would be :

select sp.hostname, sp.program_name, sp.loginame,
    st.text as query_text
from sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st  
where sp.spid = @@spid

Maybe there are too many details there..but I believe that you get the idea.

A third option would be to use the context_info information to the current SP's session. And associate somewhere the context information saved there with each procedure. For example in procedure1 you write 111 to the context, in procedure2 you write 222.. and so on.

A lot more info regarding context_info you can read in this SO question.

StackExchange Database Administrators Q#3815, answer score: 9