See what caused a SQL trigger?

Question

I just wonder if it is possible to see what stored procedure or SQL statement that caused a trigger to be executed somewhere in the trigger.

I have a really wierd bug where a field is changed and i can't for my life figure out why that is happening. I have a trigger that is logging that the data is changed but it would be great if i also could log what sql statement caused the field to change.

Is this possible?

Answer 1

I have not used SQL profiler for this purpose. But this article seems to suggest that you can do that: http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/c87621996c73012b

Look at Ivan's response... He says 'Try SP:Completed trace event. '

Here is documentation about the SP:Completed trace event: http://msdn.microsoft.com/en-us/library/ms188675.aspx

Answer 2

I've not tried this but try adding OBJECT_NAME(@@Procid) to your trigger. @@SPID can be useful as well.