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.