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.
This Post Has 5 Comments
Pingback: The Costs And Benefits Of Triggers – Curated SQL
I like triggers. Like any tool, it’s a tool. The good or bad happens when someone uses them. Back in my early database dev days, I learned the meniacal behavior of poorly written (and untested) triggers. I got wised up quickly and learned how useful they can be when written–and tested, properly.
Yeah, I was the guy who made the DBA want to strangle me. Now I’m a DBA. Yes, I have apologized to him more than once. Kind of like how a child grows up, has kids, and then apologizes to their parents for all their former childish behaviors.
Good to hear it, Cole. Testing is so important – triggers are like stored procedures and should be tested like them, but with extra care because they have a different kind of interface. Keep up the good work. 🙂
There’s an old saying “If your database is too fast and you want to slow it down just create some triggers.”
Triggers are not just like any other code. If a trigger is present on a table it triggers (pun intended) creation of pseudo tables inserted and deleted. They are created in the row version and reside in memory. (Much better then back in the days when they were created by scanning the transaction log). In any case if the data-set operations are used and they affect lots of data, those pseudo tables are large and their creation may slow down data updates considerably. Even if the triggers are disabled, they still slow the transactions down albeit less then if they are enabled. If there are more than one trigger on a table the pseudo tables will be created more than once. If they are referred more than once in the code they will be generated again each time.
I did not use them last four years so my observations might be outdated.
They have their role, but if they can be avoided, it’s better to be so.
Visibility is not an issue just execute
select * from sys.triggers
sp_helptext will show you the trigger code.
I agree that it’s easy to find triggers – it’s just that you need to think to look. People look for stored procedures, but rarely triggers. The overhead of dealing with the “pseudo” tables is no worse than TVPs when you want to deal with multiple rows in the one operation.