Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
2

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.

share|improve this question

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
1) OBJECT_NAME(@@PROCID) in trigger returns trigger name :(. 2) it is necessary to have information just at the trigger. 3) context_info is a solution. thanks. – garik Jul 14 '11 at 8:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.