05 December 2006 20:01
Tracing Trigger Execution (and code path) using SQL Profiler
Ever wondered how to trace trigger execution? It’s actually quite simple and you can get a lot of information – statements executed, reads for the statement, writes and even row counts; it just makes development and testing so much simpler (and quicker).
Anyway, here’s an example:-
create database TestTriggerTrace
create table mytest (
id int not null,
somedata int null
alter trigger test_trigger on mytest
if @@rowcount = 0
if exists ( select *
where id % 2 = 1 )
set somedata = id % 2
where id in ( select id
from inserted )
-- Determine the object id for the trigger
print object_id( 'test_trigger' )
-- Determine the database id
You need the object id of the trigger and the database id in order to trace those specific events otherwise you’ll get so much information to wade through – which could be your next step if you are tracking down a problem.
Now kick off SQL Profiler and a new trace; you need only trace the SP:StmtCompleted event because that’s where trigger execution appears. Select the columns that most interest you; in my example I’ve chosen the usual stuff – CPU, Reads, Writes, RowCounts, LineNumber and TextData which will contain the statement within the trigger being executed; with all that information and trace event you can see which statements are being executed and which aren’t.
Let’s try a test, run this…
insert mytest ( id ) values ( 1 )
Because the id is 1 and we are checking 1 % 2 which returns 1 then the trigger just returns – hence not seeing the rest of the trigger.
insert mytest ( id ) values ( 2 )
Executing that you see a different execution path through the trigger.
The end – hopefully you’ll find it useful; there is so much more that SQL Profiler has to offer you could spend a day (or more) on it!
Filed under: SQL Server