SQL Homework – September 2020 – More DML Triggers

Leave a comment

September 1, 2020 by Kenneth Fisher

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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: