I ran across a post recently where someone asked about using a trigger for a particular issue. There were quite a few responses, but one person brought up the fact that triggers have various downsides, all of which I agreed with. However I didn’t see one mentioned, which is a big one with me.
Triggers are hidden.
It’s not that triggers are hidden from DBAs by default, after all, there’s a triggers folder under each table in the Object Explorer, but how often do you open that folder? It’s just not obvious when a trigger exists on a table. The use of triggers is too uncommon, and it’s easy to forget they exist, even if you’re the person that added them to a database.
This is really my one big complaint about triggers. I can’t easily determine when a trigger is firing, which sometimes leads to issues. I’ve troubleshot strange things in SQL Servers, only to realize hours later that a trigger is firing based on some action.
Perhaps it’s me, but do many of you think about triggers first when something strange happens in SQL Server? I think I’ve gotten better over the years at looking for them, but I still get stymied and waste time trying to debug issues. I really wish that a “triggers” tab would appear in SSMS, maybe after the Execution Plan tab, whenever a trigger fired. It could have a copy of inserted and deleted, or even just the trigger code. That alone might clue me in quicker.
I think triggers can be valuable in database systems, but because they seem to be so rarely used, I’ve grown to distrust and dislike them over the years.