I wrote a Tweet about triggers yesterday that seemed to get a strong reaction, so I thought I would clarify some things. 🙂
Guns don’t kill people, people do (or is that rappers). In the same way, triggers are not inherently evil, but their misuse is. Toon Koppelaars, of Helsinki Declaration fame, wrote several post about triggers on this blog, which amongst other things highlight the problem of stigmatising something because it is being misused. With all that said, I dislike database triggers and would advise people to avoid them wherever possible.
My initial Tweet was prompted by an incident at work, where a relatively simple procedure containing a single insert caused a cascade of DML changes. The procedure did an insert into T1, but T1 had a trigger that did an insert into T2, then T2 had a trigger that did and insert into T3 etc. I think in this case there were about 5 jumps like that. This is just a nightmare for people to keep control of, and when something goes wrong it can be a nightmare to diagnose. How many times have you heard someone say, “But my code doesn’t even hit that table!”? 🙂
I’ve listed a few facts/thoughts/opinions on why you should avoid triggers here. The same article includes an example of how triggers make tracking dependencies a lot harder (here).
As Toon says, there are some very real use cases for triggers, but I think a lot of the time triggers are used as a sticking plaster to try and fix a bad job, which results in a spaghetti solution. Once you start down that road, destruction is inevitable.
Just be careful, and don’t say you’ve not been warned. 🙂
Cheers
Tim…