How to debug a T-SQL trigger?

I have a table t, which has an “after insert” trigger called trgInsAfter. Exactly how do i debug it? i’m no expert on this, so the question and steps performed might look silly.

The steps i performed so far are: 1. connect to the server instance via SSMS (using a Windows Admin account)

1. right click the trigger node from the lefthand tree in SSMS and double click to open it, the code of the trigger is opened in a new query window (call this Window-1) as : blah….,

   ALTER TRIGGER trgInsAfter AS .... BEGIN ... END
   

2. open another query window (call this Window-2), enter the sql to insert a row into table t:

   insert t(c1,c2) values(1,'aaa')
   

3. set a break point in Window-1 (in the trigger’s code)

4. set a break point in Window-2 (the insert SQL code)

5. click the Debug button on the toolbar while Window-2 is the current window

   the insert SQL code’s breakpoint is hit, but when I look at Window-1, the break point in the trigger’s code has a tooltip saying 'unable to bind SQL breakpoint, object containing the breakpoint not loaded'

I can sort of understand issue: how can SSMS know that the code in Window-1 is the trigger

I want to debug? i can’t see where to tell SSMS that ‘hey, the code in this query editor is table t’s inssert trigger’s code’

Any suggestions?

Thanks

Answer

You’re actually over-thinking this.

I first run this query in one window (to set things up):

create table X(ID int not null)
create table Y(ID int not null)
go
create trigger T_X on X
after insert
as
    insert into Y(ID) select inserted.ID
go

I can then discard that window. I open a new query window, write:

insert into X(ID) values (1),(2)

And set a breakpoint on that line. I then start the debugger (Debug from menu or toolbar or Alt-F5) and wait (for a while, the debugger’s never been too quick) for it to hit that breakpoint. And then, having hit there, I choose to Step Into (F11). And lo (after another little wait) a new window is opened which is my trigger, and the next line of code where the debugger stops is the insert into Y... line in the trigger. I can now set any further breakpoints I want to within the trigger.