SQL SERVER – How to Avoid Triggers for Multiple Row Operations in a Table

Customers have a unique way to give you challenges that you never faced. It was one of those evenings that I was working late night on some SQL activity when I received a mail from one of my very old customers. It is rare that I get a ping from them and I was surprised at what had gone wrong. As I started to chat with them, he didn’t know I had recently moved to consulting. This got him double excited and I was unaware to why such a reaction. Your best customers are your best well-wishers too. Let us read about How to Avoid Triggers for Multiple Row Operations in a Table.

History – Story of How to Avoid Triggers

Now jumping to the meat of the conversation. He was frantically looking for someone who can fix some error they were getting inside their application. I said I would be more than happy to help with their SQL Server side of things, but was not aware of their application code. The DBA said it was erroring in the SQL Server and they were not sure what the previous consultant did to their code.

Now this got me curious, I asked for what had happened with the previous consultant who made some code change. They said to improve performance the consultant had converted code from procedural to SET based operations using MERGE command. Everything seemed to work when the consultant was there and a week after he left some background process that used to run successfully is now failing.

This was a surprise and their application code logic had something that was not accounted for. Now, since the other consultant is out of the country. This was a nice troubleshooting exercise and I was wondering what could have gone wrong. On investigation and my usual analysis phase, I realized the code change was good, but it didn’t account for a trigger written on the table. Now the discussion of why this legacy trigger exists and options to change them is yet another activity – what I did to help the customer is shown below.

My learning: Triggers were written for single data operation and the fact that the trigger is not able to handle multi-row operations call for a major rewrite.

I added the below code at the start of the Trigger as a temporary hotfix:

IF (SELECT COUNT(*) FROM inserted) > 1
BEGIN
	ROLLBACK TRANSACTION
	RAISERROR('Multi-row operations on table "LOCKS" not supported', 16, 1)
	RETURN
END

And guess what the whole code was written in a notorious way, there were some sort of recursive call and they were wanting to make sure the trigger is existed on such calls. I used a simple, code to handle that.

IF TRIGGER_NESTLEVEL(@@PROCID) > 1
	RETURN

This whole exercise got me nostalgic because I have not seen extensive trigger usage in the past 4-5 years with many customers, but sometimes you get into these applications that have a legacy of more than 15+ years and these carry along their inefficiencies. Now the pseudo code in a collapsed manner looks like below:

Post this hot fix (if I can say so), now I am engaged with the customer to modernize their code and how some of these can be avoided. I am sure there is lots of learning from domain to how some of the code is written, but I am glad to have got a customer who is resuming a journey with SQL Server with me.

Will be more than happy to understand your horror stories about working with Triggers (or Avoid Triggers) and do let us know how you went about mitigating the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)