Worst Practice - Triggering External Events
Andy Warren started his worst practice series some time ago with the intention of looking at the worst things you can do. Given that we may not always be able to implement the best practices, at least we can try to avoid doing things harm the system or decrease performance.
This article continues the series and looks at an item I see mentioned in our discussion forum quite often, triggering external events. From a trigger.
What Do I Mean
The example I most often see asked a question about is sending an email from a trigger. Usually when something changes, like a new order is entered in the system or some value is changed, a developer wants to send email from a trigger with information about the new data to someone.
This can be done, and in fact, it is a pretty easy thing to do. Grant permissions for xp_sendmail and let your users get notifications. But this is a bad idea. A worst practice in system design. This applies to sending mail, running a DTS job (the 2nd most asked question), running an external program, or in any fashion triggering some type of non-SQL transactional process.
I'm not saying that sending notifications to users (or running some other process) on data change is a bad idea. Although I think this is not the best way to handle things, that's a separate discussion. I'm focusing on the use of a trigger to drive these external events.
When a trigger fires, it does so in the context of the current transaction. This means that all resources involved in the transaction, whether a simple update or a multi-step transaction, are held until the trigger completes. This means locks are held on tables, rows, pages, whatever. Blocks can quickly stack up as other processes, which are usually users, attempt to perform the same update that your session just completed. I'm not sure how many of you would want to have your end users waiting (and your phone ringing) while some cool program you wrote updates a web page.
And then consider the situation if your program fails. Suppose you are sending mail, it's been tested, works great. And then one day, for some reason, your SQL Mail doesn't connect to the mail server. Or it receives an error of some sort when trying to send mail. Then your trigger gets an error returned to it. And what happens?
It rolls back.
Which rolls back your update. Which may result in an error to your users or worse, removing a bunch of data they updated and not correctly telling them that the transaction was rolled back. Especially if it was a significant amount of data entry. Or the person is the VP of Sales 🙁
I'm sure that some of you are also thinking that some external processes, especially those that aren't spawned off on their own, shouldn't be included in triggers. But Steve, you say, what about starting my DTS job with sp_start_job. This runs asynchronously and returns immediately. However consider this fact. Jobs in SQL Server are not reentrant. This means that if a job is running, you cannot rerun the same job until the first execution completes. While this may not be a big deal, it does mean that your trigger may not work as you expect if it fires while the job is already running.
Conclusions
I'm sure most of you test your code and quality issues aren't rampant. While my DBAs may complain a lot about things that don't work, a tremendous amount of things do work well everyday and lots of data gets processed. However even the best run databases and applications in the world have bugs and giving those bugs more places to flourish is a bad idea. A worst practice.
Do yourself a favor and don't trigger anything from a trigger other than another data update. Build another process, come up with another solution, let your creativity wander, but don't include other processes in your triggers.
Steve Jones
©dkRanch.net February 2004
