01 Mar 2009 SQL Database Triggers
I hate database triggers.
Whew. It feels good to get that out in the open. I have been holding that in for almost ten years. It got harder to admit after I became a DBA, and as time went by it started to feel as if I was trapped in a story by Edgar Allen Poe. But now my secret is out, and I want to tell everyone about triggers, why I hate them, and why you should be weary of them as well.
As I mentioned before I used to develop software for a living. An actual software company, with a proper SDLC, actual Release Notes, the whole nine yards. And part of our application design was the use of database triggers to enforce business logic. Why? I don’t know, that decision was made a long time before I showed up. Truth be told I did nothing to stop the horror. In fact, I went along with it, thinking it was the right thing to do. If only I knew then what I know now, I would have fed everyone a steady diet of Jaeger and bacon until they passed out and then simply redesigned everything until they awoke from their dietary comas at which point they would have looked at me and simply said “Thank You”.
I am sure that at some point in the distant future someone will be reading these words and say to themselves “What is wrong with database triggers? I use them ALL the time!” All I can say is this: I am very happy for you. Honestly, I think that is great and the two of you will live a wonderful life together. However, most of the rest of us would rather eat shards of broken glass than create even one trigger in our databases.
I lost track of the number of hours we spent researching “bugs” in the application code at that company, only to find that there was a trigger involved. I still remember the times when we would finally realize that a trigger was the culprit and we would start to mutter under our breath “I hate triggers”. Yet we would continue to use them. I guess because by then the train had left the station and we just felt like we had no choice but to go along for the ride. My best guess is that the whole idea of triggers in a database comes from the twisted mind of the Mad Hatter, but no one knows for certain the true twisted origins of these vile objects.
Think about this for a minute. There we were, developing code and a database design that had triggers, and it was almost always the last thing we would think about when staring at data and going “WTF?” So, if triggers were always such a pain in the arse for us, how do you think they are for people who almost never use them? Sure, I am not the brightest bulb on the tree, and you could easily dismiss my frustrations as the result of an ID10T error, but how do you explain the rest of the developers there? In my opinion it was clear that relying on triggers to enforce business logic was not the best of practices.
Sure, triggers are a tool, and I know some people love them. And I also understand that there are some cases where triggers are the right tool (or only tool) for some particular job or function. But the idea that I would look to use a trigger to fill a particular need strikes me as similar to what a sailor thinks about while on shore leave in Hong Kong. Sure, you are going to get the job done, but no way would you feel good about yourself or want to tell your friends about that trigger. And before you start to tell me about how you rely on triggers to ensure referential integrity let me stop you and point out that primary and foreign keys also perform that functionality. What’s that? Oh, you need to keep your integrity across databases? Fine, go ahead, use a trigger, and good luck to the people who maintain that database when they try to understand who the hell is manipulating their data.
So, what is my real problem with triggers, besides the idea that I could need shots of penicillin after creating them? They are impossible to track and troubleshoot. I have used many tools to create basic E-R diagrams in my life. Do you know how many of them would contain primary and foreign key relationships? All of them. Do you know how many contained triggers? None. Sure, some software will let you know a trigger exists on a table in an E-R diagram, but they do not give you the full story, and it just makes it harder to figure out the real picture.
And why should it be so hard for us to troubleshoot them? Just thinking about how much time I have lost to tracking down data changes being done mysteriously makes me want to reach into SSMS and choke the Database Triggers directory. Here is a thought, why not have a system view that would allow for me to immediately see the data that has been modified by a trigger? I am not saying to show me a list of triggers, because we have already seen that just knowing they exist is not enough. No, I am proposing that every table in the database have a column attached that shows us if the last update on a row was a result of a trigger action.
Sounds similar to auditing, right? Perhaps. And perhaps such a view already exists, but I have not seen one. But it would seem to me that it would be simple to track. Maybe it would make more sense to have this column created only when a trigger is defined on a table. That way when I make a change to some application code, examine some data after pressing a few buttons, I would be able to see that the data that was changed was ultimately the result of a trigger action, and I could stop ripping apart my application code wondering why the data was altered unexpectedly. But since I am not aware of a way for me to see the end result of a trigger action, I hate triggers.
Almost as much as I hate NULLs.
Don’t get me started on those bastards.