This month’s T-SQL Tuesday is brought by Steve Jones (b | t) and he wants to know if triggers causes headaches or happiness to us.

This is the 106nd edition of TSQL2sDay – an Adam Machanic (b | t) brainchild.

 
Triggers are those kind of database objects that sometimes are the best bet but most of the times they will strike back.

“Why?” – You may ask.

From my experience triggers are objects that we “tend” to forget are there, and only when we hit a problem and, sometimes, after digging into the problem we end by saying something like

wait..but why is the value different from what I have used?! Ohh…maybe the table has some triggers.

Yes, it has already been uttered by me before. For me, triggers are a hole of possible business logic written and forgotten…forever.

Let me just clarify the meaning of “forever” here – until you hit a problem or you need to rewrite some logic and the result is not what you expect. Again, because the code is ‘hidden’.

Recent pain – A project to migrate Firebird SQL to T-SQL

When Steve mention the “I think I’ve ended up using triggers in 0.01% of my tables or less.” I remember this project and after the math I can say they used ~78,84% and ~63,32% (were two databases) of triggers on their tables.

When looking at their code it became obvious the type of use they are making, mainly to fill 1-2 columns with current datetime and/or to generate a new ID (because they didn’t use identity columns – supported on v3.0 or higher)

“You never used triggers, it’s what you are saying?”

No, it’s not. I have used them before and not only 0,01% but maybe less than 5%.

Let me share an story

Back in the earlier times when I was a full-time T-SQL developer (I think was on 2008/2009), I worked with an application that use triggers to insert data on some tombstone tables that help to go back and get the data at that time.

Nowadays, we can achieve this with much less code using temporal tables.

One day I hit an issue where the record on the main table wasn’t being inserted, I run the code, a stored procedure, with some parameters and I got back a new ID (we had identity columns) all good. When I ran with other parameters (the ones not working) I didn’t see the new ID, I went back and forward and seeing the gaps generated by the problematic parameter set, throw some debug messages into the stored procedure, also throwing more information on the catch block to understand the problem.

Then, after reading the message carefully I saw that the problem was on the insert on my tombstone table and not on my “visible” logic. Spent about one hour to get it…

Waste of time

Or not, from that day on, one of my first questions when I hear people have some problems like this is to ask “are there any triggers on the tables involved?”

With that I already saved some hours of work.

Wrap up

As you may understand I decided to not use triggers whenever possible, but, sometimes we pick the train already in movement and we may need them.

If you fit on this last scenario, please, please document it very well!

I’m eager to read other’s stories because I bet they also have some good points and on this interesting topic.

Thanks for reading.