Tony Rogerson's ramblings on SQL Server

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!