05 December 2006 20:01 tonyrogerson

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


use TestTriggerTrace


create table mytest (

    id  int not null,

    somedata int null




alter trigger test_trigger on mytest

    for insert



    if @@rowcount = 0



    if exists ( select *

                from inserted

                where id % 2 = 1 )



    update mytest  

        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

print db_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:


# Log Buffer #22: A Carnival of the Vanities for DBAs

08 December 2006 17:05 by Brian Kelley

Welcome to the 22nd edition of Log Buffer, a weekly compendium of postings and news from database-related...