T-SQL Tuesday #106 – Triggers Headaches or Happiness

This month’s TSQL2sday is hosted by Steve Jones (b|t) and this time the topic is the experience we all have with database triggers, one of the most controversial database objects you can work with…

DML, DDL, and Logon

There are three kinds of triggers in SQL Server. From docs:

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. […]

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. Logon triggers fire in response to the LOGON event that is raised when a user’s session is being established.

In this post, I will focus only on DML triggers, because they are the most “interesting” ones.

The Good, the Bad and the Ugly

The age-old question that every database developer and administrator asks himself is if it is OK to use triggers? And frankly speaking, there is no simple and short answer to this. On the Internet, you can find almost every opinion. There are a lot of voices saying that triggers are evil and everyone should avoid them as much as possible. However, you can easily find opposite views – that they are useful objects and there is no reason to not use them. If you take a look at this question (Are database triggers evil?) on StackOverflow and go through the answers and comments you will see what I’m writing about.

So how is it? Why is it so hard to decide whether triggers are good or bad? Usually, in such cases, we all (especially in IT) used to say: “It depends!”. It depends on the reason why you want to use them and the goal you want to achieve. They are a tool (like a hammer), and as any other tool they can be used to do something good (i.e. hammer a nail) or to do something bad (i.e. break the glass). Everything depends on the intention and the usage.

When triggers may be a bad idea and why?

The biggest problem with triggers is that they are often being left alone and forgotten, and like a silent assassin they wait in the shade to backstab you when you do not expect that. Usually, when something does not work and you need to investigate it, it is very common to focus first on tables’ structures, functions, procedures, and constraints. I’d already caught myself in such a trap a few times. Finally, I’ve recalled checking triggers when everything else was already checked but I could save a lot of time if I did it earlier.

Secondly, it is really very hard to debug them. Especially in the case of nested or (even worse) recursive triggers (Have you watched “Inception” movie?). There are no tools that can help you with such a hard task.

Thirdly, triggers are executed in the same transaction context as a DML statement that triggered them. It has some benefits (i.e. you can rollback this statement) and drawbacks (complicated logic can cause performance issues or even deadlocks).

Taking all of that into consideration, my recommendation is to not create triggers that:

• Implement business logic required by an application to work properly;
• Check consistency of inserted data;
• Modify data on-the-fly when they are being inserted;
• Implement any other logic that can be executed in Stored Procedure executed before DML event occurs;

When triggers may be a good idea and why?

However, there are situations when triggers use can be justified (to some extent). What are these situations?

• The most popular case is to log changes of your data to some logging table.
• Another case is when you need to create a temporary solution for some issue and you cannot change existing database objects.
• The third one is… nope, there is no third one.

In my opinion, triggers may be used only for such purposes, any other things should be done explicitly in Stored Procedures.

My experience

Usually, I tend to remove triggers from my code base rather than create them. When I decide to create a new one, in more than 90% of cases, I use them for logging purpose as I described it in the previous paragraph. The next 9% are temporary fixes. Every time when I create such a fix I have mixed feelings. On one side I’m proud that I’ve solved an issue and thanks to that the application works fine. On the other side, I’m really ashamed of it. I know it’s a dirty hotfix and I pray that nobody considers it permanent. What about the remaining 1%? These are ugly things I don’t want to speak about…

But to be completely fair I also need to admit, that sometimes working on a trigger may be quite interesting. I remember two such cases when it was really entertaining.

Logging

The first case was when my team got a task to create a “transparent and dynamic” logging mechanism for one of the tables in our system. It had two main requirements. The first one was to have a solution that handles situations when new columns are added to this table without changing the trigger’s code. The second one was to log only this data that really changed. The table consisted of more than 100 columns and its data was very frequently modified. With the ‘standard’ approach logging table was very quickly getting bigger than the monitored one.

Non-overlapping  time intervals

The second case was when I was asked to simplify an old trigger code that was created to ensure that inserted time intervals were not overlapping with the existing ones. It had implemented logic to merge, split, delete, and create new time intervals according to defined rules. At that time it took me 1 week just to analyze existing implementation that looked like someone obfuscated it.

If you find these use cases interesting and would like to read more about them, leave a comment and let me know. 

Thanks for reading!

Hey Twitterverse,

Quick poll. What do you think? Database triggers are…#sql #Database #SQLServer

— Marek Maśko (@MarekMasko) September 8, 2018

-Marek

Share it: