1

I've got a complex table interaction where an insert onto my first table will fire triggers that update several other tables, which in turn can activate triggers... etc.

Is there a way that I can search for every trigger that fired during the execution of that insert query?

Zeckal
  • 145
  • 1
  • 9
  • 4
    Sure, create an Extended Events session that captures module_start, filtered to your session_id, then run the insert. – Stuck at 1337 Aug 23 '23 at 17:30

1 Answers1

1

You can look into sys.dm_exec_trigger_stats in order to get information of any trigger that's currently stored in memory. It container one row per trigger. See more here: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-trigger-stats-transact-sql?view=sql-server-ver16

Now, one may wonder how can we differentiate triggers that are triggered by our action from other triggers that happen to be triggered around the same time. We may have a separate, dev copy of the same database, in which case it's simple to determine by running the action and only the action we are interested about.

If you use SQL Profiler, then you may want to check this: How to get SQL Profiler to monitor trigger execution

which claims that profiling triggers can be done via

Stored procedures: - SP:StmtStarting - SP:StmtCompleted

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175