SQL Server Profiler – Showing Triggers and Side-Effect Code

profilersql serversql-server-2008-r2

I've been using profiler to test a massive script I'm running in order to check for unintended side effects, e.g. triggers that I don't know about running because I've updated data in a certain table.

Having executed the script while the profiler is running, I can't see any additional code executing but I don't know whether that's because there is none or because profiler wouldn't show it for some reason!

I restricted the profile to these events:

SQL:BatchCompleted
SQL:StmtCompleted
SP:Completed

Am I missing events which might show me that a trigger has fired or can I be confident that nothing is happening which I'm not expecting?

Best Answer

That should capture any ad hoc SQL or stored procedures any triggers associated with the table will fire, depending on the filters of your trace (I would be more concerned that it captures too much for you to sift through).

But there is a much cleaner and less invasive way to know if a table has a trigger associated with it:

SELECT [Trigger] = tr.name
FROM sys.triggers AS tr
INNER JOIN sys.tables AS t
ON tr.parent_id = t.[object_id]
WHERE t.name = N'TableName';

There are multiple other reasons the profiler trace is unreliable - a trigger could be currently disabled, for example, or the trigger might only run further SQL statements if certain conditions are true.

Related Question