It's a holiday in the US today, which means we're taking a 3-day weekend to dig back through the archives and find a classic WTF. One of my favorite features- one that we run far too rarely- are the true confessions. Sometimes, we are TRWTF, and let's applaud Matthew Schaad's story about his misuse of database triggers. - Remy

It started out as an average day for a developer like me. At 11:30AM, I was just getting into the office and fixing my second cup of coffee for the day. Being in the habit of coding till 3:00AM nightly, I was averaging about three to four cups a day. As I sat down at my desk to tackle one the several projects I had been assigned, I got a frantic call from the Director of IT, Jeremy.

“Matt!” the telephone receiver shouted.

“Yes?” I responded coolly, as I was used to hearing about fires that needed to be put out yesterday.

“The reservation system is not allowing users to delete their reservations! Martin booked a conference room every Thursday for several years out, and now he can't delete it. Can you take a look at it?”

“Sure, no problem,” I replied before hanging up the phone and going back to sipping my coffee.

Jeremy was referring to our company reservation system. Similar to Outlook, we use it to manage shared resources, such as conference rooms, company cars and IT equipment. It isn't the most reliable system, and we constantly get complaints about it. Most of the time, though, the problems seem to arise between the chair and keyboard.

So having gotten my marching orders, I opened up the database, wrote a quick DELETE statement, ran it, and saw that it deleted all the records for Martin’s recurring appointment:

(1137 row(s) affected)
Query executed successfully.

Man, that is one long-standing appointment, I thought to myself, but problem solved! I called Jeremy to tell him it was fixed.

I got a call later from Jeremy telling me that the problem was very much not solved. The event was still appearing in the system. Whoops. How embarrassing!

What? I could have sworn I took care of that, I wondered as I sipped my fourth cup of coffee. I went back into the database, this time making doubly sure that I was on the correct server, and wrote the DELETE statement again. Not wanting to embarrass myself this time, I ran a SELECT against the reservation table just to make sure I wasn’t declaring success prematurely again. I was shocked to find that the query still returned over 1100 records! I blinked.

I ran the DELETE again. The server reported deleting the 1100 records successfully.

I ran the SELECT again. 1100 records returned.

I stared slack-jawed at the screen. I had never seen this sort of problem before.

I ran the DELETE again. This time, I looked closer at the message:

(1134 row(s) affected)

“Well, some of the rows are getting deleted,” I mused.

I ran the DELETE statement twice more.

(1133 row(s) affected)
(1132 row(s) affected)

“There’s one fewer record every time,” I said to myself. “They’re getting deleted one at a time!”

What in the world could cause this sort of behavior?, I thought myself as I rubbed my chin. The more I pondered the problem, the more I realized there was only one answer: a trigger. So I opened up trigger list on the reservation table, and sure enough, there were three triggers.

sendEmailAfterDelete
sendEmailAfterInsert
sendEmailAfterUpdate

Guessing that sendEmailAfterDelete must be the culprit, I opened up its definition and saw that it started like this.

CREATE TRIGGER [dbo].[sendEmailAfterDelete] ON  [dbo].[reservation] INSTEAD OF DELETE

The author of that trigger had created it as an INSTEAD OF trigger, rather than an AFTER trigger, as its name implied. That meant that only the trigger code would run, and not the delete statement.

INSTEAD OF DELETE? I thought to myself. Well, that could definitely do it.

I examined the body of the trigger for logical errors, but I didn’t find any. It was a straight-forward trigger: SELECT the first deleted record from the SELECT pseudo-table, send an email to the appointment owner, then actually DELETE the record. The INSTEAD OF declaration was a simple mistake that anyone – even me – could make.

“Waitasec,” I said aloud, “we’re sending email messages from a trigger in the database!?!”

It was one of the worse anti-patterns I had in our database, but now was not the time to fix it. Instead, I did a simple ALTER TRIGGER to have the code loop over every row instead of just applying to the first one. I then ran the DELETE for Martin’s appointment one more time:

(1131 row(s) affected)

I ran the SELECT for Martin’s appointment one more time:

0 rows

Success! I called Jeremy, and told him the good news. But my quest was not complete: our database committing a WTF of epic proportions and sending emails in a fairly-hidden delete trigger. I had to find the idiot who thought this was a good idea, school him on the most basic principles of software design, and make him fix it.

But then all of a sudden, I had a flashback to work I had done on the system a few years ago. I remembered that I was the idiot that had written the trigger to send emails when reservations got deleted. I wish I could remember my mindset at the time, but I suspect it just seemed like a neat trick.

I stayed late that day fixing the email notification and, as I tested the new code, I wondered what ever happened to Martin’s inbox.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!