Long time readers might remember my posts from years ago about implementing Table-Valued Parameters in SQL 2005, or about TVPs in SSIS, and think that I have some interesting views about triggers. I’m not sure whether Steve Jones (@way0utwest) knew this when he decided on this month’s T-SQL Tuesday topic, but either way, it’s been a long time and triggers are worth talking about.
They’re worth talking about because people misunderstand them and misuse them. And therefore they are hated.
Triggers are code that is executed when something in the database happens, such as an insert, delete, or update for DML triggers, or created, dropped, and so on for DDL triggers. The event ‘triggers’ the action, hence the name (but don’t get me started about the fact that the ‘trigger’ is not the thing that does the triggering; it’s the action).
And therein lies the problem. Someone does something which seems to be innocent, but then finds that some code has started executing, and the innocence of their tiny operation is lost. And who knows what awful things are done!? And all within the same transaction as your innocent little update…
But the problem isn’t what they do, it’s that you didn’t know it was there. Just like asbestos.
People complain about nested triggers, about triggers that are overly complex, about triggers which do too many things… but stored procedures have all these faults too. It’s just that you knew that the stored procedure was there, and you didn’t know that the trigger was there.
I wish that triggers were more visible inside the tools (Management Studio / Operations Studio / Visual Studio), right alongside stored procedures and procedural functions (those ones that use BEGIN & END and are bad, rather than inline functions). They’re code and should be treated as such.
Once you start to see triggers as code, and particularly if you use INSTEAD OF triggers rather than AFTER triggers, you can start to see them as being not dissimilar to stored procedures that have access to handy internal tables. In those two posts I linked to at the start, I show ways you can use triggers to effectively have table-valued parameters without user-defined table types. I think this is really handy and somewhat elegant, except that the code isn’t obvious. Of course you can document them, and find ways to let your DBAs know they’re there… but still they will be considered semi-visible, and be nefarious by default.
Like many features of technology, they will be used by well-meaning developers, and often the impact will be negative rather than positive. You may not be able to change the fact that they’re used without refactoring major parts of the architecture (again, like asbestos), but there are some things you can do:
1. Be pragmatic. Accept that you will come across systems that use triggers. Don’t overreact or ask that developers be fired – remember that you’ve seen worse.
2. Document them. Make sure that you don’t forget they’re there, and that those that come after you can discover them easily.
3. Learn to appreciate the power of them. I’m not saying you should start implementing them, unless you’re pushing data around using SSIS and are wishing you had TVPs, but you could look at them as being like stored procedures and appreciate that they could be an elegant solution.
4. Tune them. You tune the stored procedures in your system, and you can tune the triggers too. Every bad thing you come across is an opportunity to demonstrate your strength as a DBA.
Thanks again to Steve for hosting this month’s T-SQL Tuesday. For more on triggers, go and check out the other posts this month. I’ll leave it up to you to decide whether triggers are good or bad – I think it’ll depend on how they’ve been done.