Find a trigger trigger request

Question

Possible duplicate:
Create a trigger to register SQL that affected the table?

Is it possible in SQL Server 2008 to write a trigger that will put in my log table the query that called it? I will explain this with an example:

I have a table, TAB1 and LOG log LOG. I am executing a query that removes rows from TAB1, and I want to have this query (or something else that can help me determine who executed the delete request in TAB1) in my log table.

Any idea how this can be achieved?

Answer 1

the only way that it seems to me is to pass the procedure name explicitly through CONTEXT_INFO it goes into the procedure

declare @bin varbinary(128) = (cast(object_name(@@Procid) as varbinary(128)) set context_info @bin 

and use it in a trigger

 declare @procName as nvarchar(max) set @procName = cast(context_info() as nvarchar(max)) 

I do not know another way to do this.

edit: I think you might be interested clickie