June 4, 2020 by Kenneth Fisher
Triggers. Blech. Triggers are a really really cool feature of SQL Server, that are continuously misused and cause all kinds of problems. So this month I’d like you to play around with them a bit. Get a feel for what they can do, and what can go wrong. Specifically we are going to work with DML triggers. These are the triggers that get attached to a table and fire when you do an INSERT, UPDATE, or DELETE command.
The most common uses I’ve seen for triggers is auditing. For example inserting a row into an audit table every time a change is made, or updating LastUpdateDate and LastUpdatedBy columns. You can try that here, or anything else you want. Here is what I want you to do.
Useing SET STATISTICS TIME ON I want you to see how long it takes to insert 1, 1k, and 100k rows into a table. Next I want you to create a trigger on that table. Make sure that it is an INSERT trigger of course or this won’t have much effect. Now I want you to run the same number of row inserts again. See any time difference?
Next create two more triggers. Also INSERT triggers. See if you can figure out how to set the order they run in. Now run the time trial again. Are you seeing a time difference now?
Also, make sure that your triggers are doing what you expect them to do. A common mistake with triggers is to assume only one row is being inserted, updated or deleted at a time. It’s not always the case obviously, so for your multi row inserts make sure you are getting the results you wanted.
Another important thing to remember about triggers is loops are slow. I will very rarely tell you that loops are bad, they are a useful tool and have their uses even in the database world. In triggers however, yea, they are bad. Because they are slow. If your trigger causes a dramatic increase in duration between the 1 and 1k and in particular the 100k runs then you may have a loop.
Regardless, I’m hoping one of the things you have noticed is that triggers decrease your insert speeds. The code that runs in a trigger has to complete before your command is done.
If you feel like you want to play with triggers some more (and I would certainly never discourage extra practice) try the same set or runs with UPDATE and DELETE triggers/statements. The only reason I started with INSERTs is because there is a limited amount of setup required. With the UPDATE and DELETE versions you’ll probably want/need to reset your table after each timed statement.