Database Triggers : Use at your own risk!

error-24842_640I 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…

Two New Articles: UDEV and Database Triggers…

I’ve recently put a couple of new articles about old subjects on the website. In both cases, the articles were initiated by forum questions, but the explanations became too painful in the format of a forum post so they graduated into articles…

  • UDEV SCSI Rules Configuration In Oracle Linux 5 : For those of you that like to follow my Virtual RAC guides, but don’t like using ASMLib, you can use this article and replace ASMLib with UDEV.
  • Database Triggers Overview : This is really a primer on database triggers. I’ve focussed mostly¬†on simple DML triggers, since this is what the vast majority of trigger-related questions I’m asked relate to. Consider it the “minimum” you should know before you write a database trigger.

Cheers

Tim…