1

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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