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

go

use TestTriggerTrace

go

create table mytest (

    id  int not null,

    somedata int null

    )

go

 

alter trigger test_trigger on mytest

    for insert

as

begin

    if @@rowcount = 0

        return

 

    if exists ( select *

                from inserted

                where id % 2 = 1 )

        return

 

    update mytest  

        set somedata = id % 2

    where id in ( select id

                  from inserted )

 

end

go

 

--  Determine the object id for the trigger

print object_id( 'test_trigger' )

go

 

--  Determine the database id

print db_id()

go

 

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:

Comments

# 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...