All triggers have to be able to handle multiple row operations.

1

September 3, 2020 by Kenneth Fisher

Quick post today. Recently I was helping a co-worker write a trigger. Basically there is a table of servers that’s maintained as part of the server creation/decommission process. There is a trigger on the table that is part of an automated process that looks at a flag in the table and does some work if the flag is positive. The users flip the flag from a screen in one of our apps and the app will only allow them to flip that flag for one server at a time.

While I was helping him with part of it I noticed that the trigger was only set up to handle single row updates. When I told him it needed to be able to handle multiple row updates he asked me in all seriousness, “Why? Since only one value can be changed at a time why should I worry about multiple rows being changed at once?”

“We have a couple of thousand servers in that table. What happens if management wants to run the automated process on 500+ of the servers. Are they going to look up each server one at a time or ask you to do an update outside of the app?”

Should operations like that be run from inside the app? Well, there are arguments for that. I mean one would assume there is a fair amount of business logic inside the app that needs to happen. That said, there will almost always be an exception. It may not be now, it may not be for a couple of years, but eventually someone is going to do a multi row insert or update on that table. In 30+ years as a DBA I’ve seen a handful of cases where there were only ever single row updates. Chances are your table isn’t going to be one of them.

One thought on “All triggers have to be able to handle multiple row operations.

  1. […] Kenneth Fisher has a public service announcement: […]

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 )

Google photo

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

Twitter picture

You are commenting using your Twitter 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,155 other followers

Follow me on Twitter

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