The Trouble With Database Triggers

Imagine you are developing a web application. You spend a few hours putting together some pages and now you want to verify the data is inserted and updated correctly. You enter some data, click the Submit button, and go to the database to confirm that the data is there. Only it isn’t there, at least not the data you were expecting.

You then spend the next few hours debugging your code. You step through every line imaginable. You call home so your kids can hear the sound of your voice because you know they will be in bed if and when you should ever get home. And then, out of nowhere, you get an idea. Could a database trigger be changing the data? Sure enough, you find that a database trigger has indeed been at work all along. Your code is working as expected. You’re just not seeing the expected results.

And that’s the experience I’ve had with triggers—and why to this day I feel that database triggers are usually more trouble than they’re worth. Why no love for these seemingly benign and inanimate pieces of a database design? Because they’re typically built in so that you don’t even know they’re there. And that results in a waste of time when trying to troubleshoot application code, for example. And I hate wasting time.

You’re probably saying, “That’s the point of triggers—you’re not supposed to know they’re there.” Fair enough, but that doesn’t mean I have to like them. It’s the same as shoving peas in my son’s mashed potatoes: He doesn’t know they’re there either, but once he finds out, he tries to remove them as soon as possible.