SQL Triggers – a Love Hate Relationship
I have a love/hate relationship with using triggers in SQL. It doesn’t matter what SQL engine I’m using.
Why do I love triggers?
Triggers are great for extending legacy systems when you don’t have access to the source of data, or modifying the Create/Update/Delete statements is either out of your reach or too cumbersome to modify. So, when you are interacting with legacy systems you may have to use triggers as a temporary solution to extend the capabilities.
Triggers may also be used as a technique for monitoring data modifications. Granted, there are built in capabilities in some engines for monitoring data changes. Sometimes that is not the best fit.
There are probably a few other good uses that I can’t think of right now.
Why do I dislike triggers?
Triggers are often use as a crutch for covering up bad schema or bad architecture. Applications with CRUD statements embedded throughout instead of calling a data access layer of some sort are a good example. Those beginning web coders with tons of SQL code in the Code Behind of their web pages are soon overwhelmed with the cost of small changes to the database, because the consuming code is widely distributed.
Triggers are not readily visible, and therefore cause a great deal of time lost for users having expectations of different behavior because they don’t know a trigger exists.
Some developers call long running processes from a trigger, not understanding the blocking that occurs waiting for the trigger to complete. A good example of what NOT to do would be to send an Email in a trigger.
Most non-SQL developers write triggers incorrectly. A trigger MUST be set based. There have been many times I have had to fix triggers written by others because it was written from the perspective of only a single row being modified. If you are working with a stored procedure you have a better understanding of the number of rows to be modified. With a trigger, you have to assume you are processing more than one row.
What is my preference?
Put your logic in your data tier. Let the database do what it is really good at: Managing referential integrity; Create/Retrieve/Update/Delete operations; Joins; aggregations and summaries. Putting all your business logic in a data tier allows you to manage your logic in one place, not in a combination such as Business Logic, stored procedures, triggers, functions, etc.
The best value for this common design using patterns such as the repository pattern is that there are great tools out there for implementing it, and that it provides you with the flexibility to use different data engines. With the engine wars and pricing increases going on today it is more likely than ever you may have to port your application to use a different storage engine than ever before.
That’s my take on triggers; use them; don’t abuse them. Share your opinion in agreement or not, by leaving a comment below. Or, feel free to drop an Email to me at btaylor@sswug.org.
Cheers,
Ben