infinite loop in an sql server trigger

Hi.

I've spent some time writing sql server triggers these days and I made the same mistake twice in a month, so I thought I'd keep a note about it here.

In these triggers I have a main loop on the records that were inserted / updated / deleted.

To write this loop I :

• defined a cursor CurIns on the special 'inserted' table
  
• fetched the elements and put them into variables (FETCH CurIns INTO @var1, @var2, ...)
  
• iterated with a

WHILE(@@FETCH_STATUS = 0)
BEGIN
[...]
END

At the end of the loop I had to write the FETCH CurIns INTO [...] again, not very DRY.

Nor especially pretty.

For some rows I did not want to execute the whole loop, so I used the CONTINUE keyword, as I would in php :

foreach ($array as $element) {
    [...]
    if (doNotNeedToHandleThis($element)) {
        continue;
    }
    [...]
}

I tested my trigger via the browser, and the page just kept loading and loading and loading.

I had an infinite loop, because I should have called the FETCH CurIns INTO [...] before CONTINUEing. So my @@FETCH_STATUS was always zero, and the loop kept iterating on the same row.

So instead of writing the FETCH statement a third time I just enclosed the body of my loop in an IF block.

Beautiful.

I found it hard to avoid repetition, in these triggers.

Oh, and the process seemed to just go on and on, I don't know if there is a timeout for this kind of thing. To find out the process id :

EXEC sp_who2 'Active'

I found the SPID based on the time and user and asked the DBA to kill it.