How to get SQL Profiler to monitor trigger execution
Stored procedures: - SP:StmtStarting - SP:StmtCompleted
In SQL Server Profiler 2008, when starting/configuring the trace, go to the "Events Selection" tab, click on the "Show all events" checkbox, and then in the list under the Stored Procedures section select the SP:StmtStarting and SP:StmtCompleted events to be included in the trace.