As a SQL Server consultant, I often come across unique challenges from my customers. Recently, I received an email from one of my long-time customers, seeking help with an error they were experiencing in their application. Little did they know that I had transitioned to consulting. This unexpected encounter led me to an interesting troubleshooting exercise, which I would like to share with you.
The customer explained that after a previous consultant made some code changes to improve performance, a background process that had been running successfully started failing a week later. Intrigued, I delved into the issue and discovered that the code change involved converting procedural code to SET-based operations using the MERGE command. While the code change seemed sound, it failed to account for a trigger that was written on the table.
Triggers are typically designed to handle single data operations, and when faced with multi-row operations, they often require a major rewrite. To address the immediate issue, I implemented a temporary hotfix by adding the following code at the start of the trigger:
IF (SELECT COUNT(*) FROM inserted) > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Multi-row operations on table "LOCKS" not supported', 16, 1)
RETURN
END
Additionally, I discovered that the trigger had been written in a convoluted manner, with some sort of recursive call. To handle this, I used a simple code snippet:
IF TRIGGER_NESTLEVEL(@@PROCID) > 1
RETURN
This troubleshooting exercise reminded me of the extensive usage of triggers in legacy applications, which I hadn’t encountered much in recent years. However, some applications carry a legacy of more than 15 years, and with them, their inefficiencies. In this case, I am now working with the customer to modernize their code and find ways to avoid triggers altogether.
I would love to hear your horror stories about working with triggers or how you have successfully avoided them. Feel free to share your experiences and insights in the comments below.
Thank you for reading!