RSS Feed

Cranky Series: Triggers Don’t Send Mail


August 13, 2012 by Mike Hillwig

Welcome to the Monday edition of my How Not to be a Cranky DBA series.

SQL 2000 wasn’t exactly the most stable version of SQL Server in Microsoft’s history, but it sure taught us a lot of things. For me, right after causing my fear of clusters, it was sending e-mail. The dark days of CDO objects and installing Outlook on servers may be over, but I’m still pretty averse to SQL Server and mail via triggers.

One day many years ago, I had a boss who loved triggers. He had me writing a trigger that fired off an alert when a certain condition was met in the system. The logic was ghastly, but worse yet, I was testing it during a time when our Exchange server was going up and down more times than a yoyo.  I saw just how ugly performance was and I saw what happened when the Exchange server went down. It wasn’t pretty.

It led me down a path where I had a conversation with the manager in the business who was to be one of the recipients of said e-mail alert. I asked her if she needed to get the alert immediately or if a fifteen minute delay was acceptable. She laughed, telling me that if she got the alert at the end of the business day, that would be perfectly acceptable. This led me to the realization that a trigger-based e-mail was not the most appropriate way to handle this situation. Instead, we had the trigger write to a queue table and then later had a SQL Agent job sweep the queue. Life was suddenly much better.

When someone says they want to have a trigger fire an e-mail, my first question is about the urgency. Then I ask what happens to the underlying application if we suddenly find ourselves unable to send mail.

There are better ways. You should find them.