Professional SQL Server™ 2005 Programming

13.9. Debugging Triggers

If you try to navigate to the debugger for triggers the way that you navigate to the debugger for sprocs (see Chapter 11 for that) or functions, then you're in for a rude awakening — you won't find it. Because trigger debugging is such a pain, and I'm not very good at taking "no" for an answer, I decided to make the debugger work for me — it isn't pretty, but it works.

Basically, what we're going to do is create a wrapper procedure to fire off the trigger we want to debug. Essentially, it's a sproc whose sole purpose in life is to give us a way to fire off a statement that will let us step into our trigger with the debugger.

For example purposes, I'm going to take a piece of the last bit of test code that we used in this chapter and just place it into a sproc, so I can watch the debugger run through it line by line:

ALTER PROC spTestTriggerDebugging
AS
BEGIN
    -- This one should work
    UPDATE Products
    SET UnitsInStock = UnitsInStock - 1
    WHERE ProductID = 6;

    -- This one shouldn't
    UPDATE Products
    SET UnitsInStock = UnitsInStock - 12
    WHERE ProductID = 26;
END

Now I just navigate to this sproc in the Server Explorer, right-click on it, and select Step Into (see Figure 13-6).

Figure 13.6. Figure 13-6

Click Execute at the dialog (we don't need to enter any parameters). At first, you'll just be in the debugger at the beginning of the sproc, but "step into" the lines ...