Triggers. Still blech. In June I asked you to play with DML triggers. Well, one more time.

First of all did you know you can do AFTER or INSTEAD OF triggers?

Triggers are a really really cool feature of SQL Server, that are continuously misused and cause all kinds of problems. I’m guessing that last time you wrote AFTER triggers. Basically triggers that run after the DELETE, INSERT or UPDATE. So this month I want you to a set of INSTEAD OF triggers. These triggers completely replace the command so you need to be somewhat more careful with them. And given that you should be really careful with triggers that’s saying something.

A common use for these triggers is to apply more advanced logic for INSERT, UPDATE and DELETE against a view. So give that a shot.

While time is still important and you absolutely need to pay attention to how long your trigger is going to take I’m not as worried about that this time. Because these triggers completely replace your command you have to be extra careful that they do what you expect. On an up note, from what I’ve seen people are more likely to write a trigger that can handle a multi-row operation with INSTEAD OF triggers than they are with AFTER triggers. Don’t forget though!

Write yourself at least 3 different INSTEAD OF triggers and run tests against them with 1, 100 and 1k rows. Make sure they have done what you expected. And as much as we aren’t concentrating on time as much this month, how did it do? Do you feel like even at a thousand rows it was a reasonable amount of time to run the command?