An Eye-Opening Look at Two Powerful MS SQL Server Features: Triggers and Database Mail

The idea is simple. Something happens in your CRM database and you want to automatically notify someone of that something. And you want it to happen immediately. In the past, using older GoldMine systems accomplishing the above was quite difficult, if not impossible. But now that GoldMine Premium Edition is standardized on Microsoft SQL 2005/2008, GoldMine administrators have access to two powerful features that make the above possible, and not too hard to implement.

 First, let me explain the concept of a database trigger. A trigger is a set of instructions that can be attached to a table in a database. These instructions are fired or triggered by certain actions taking place in the database. Typically, there are three types of triggers: insert, update, and delete. Each type of trigger fires respectively whenever records in that table are inserted, updated, or deleted. So let’s say you want to get an Email notification whenever a customer service issue is logged. (I’ll assume you are using GoldMine’s Case Management to log customer issues, but this would work just as well with History records.) We would create a trigger on GoldMine’s CASES table that would fire whenever a new case is entered into the system. You can build logic into the trigger to tell it things like: When to fire, when to do nothing, what data to pull from the Case, Contact or other data tables. This leads us into the Email part of our solution: Database Mail.

 Introduced with Microsoft’s SQL Server 2005 release, and obviously branded by their technical writing department, is a new feature they call “Database Mail.” Database Mail allows an Email to be constructed and sent programmatically through a series of SQL commands. It is compatible with most Email servers. It uses SMTP protocol and has various authentication options. But the really cool part is you can use the code in your trigger to assign and Email’s sender, recipient, subject line, message body text, etc.  ­– all dynamically using info that the trigger is pulling from the database! So in that customer issue Email you could see the customer’s name, the case number, case description, the user who entered the case into the system, and virtually any other data you could pull from the database.

  The above is just one scenario among a sea of possibilities. A notification could be triggered on a completed sale, an accounting/billing issue, a new customer assigned to a sales rep. I’m sure it wouldn’t take managers, business owners, or database administers long to ponder and think of a situation where a database trigger with a coinciding alert would be very useful within their organization.