Today's story starts with a SQL trigger, and a Dynamics GP service pack which dropped the trigger, unbeknownst to me. Fortunately, I had planned ahead, not wanting to rely on my (or anyone's) memory to remember to check for issues after installing service packs!
A few months ago, I posted about the issue that led to this particular trigger in the first place. Its sole purpose is to correct an issue in Dynamics GP that may or may not be a bug! This post is less about why the trigger exists in the first place and more about how to identify when a trigger has been dropped from a table.
A caution about SQL triggers
In the Dynamics GP world, depending on what tables the triggers are created on, those SQL objects may be dropped after the install of a service pack. Tim Wappat wrote a post a few years ago about this very thing, with some tips on scripting out triggers before upgrades.
I have a healthy skepticism for SQL triggers. I have had situations where they have been very useful, if not instrumental, in offering a client some functionality that would have been difficult any other way. In every situation where I have recommended one, I have also stressed to the client the potential negative above. It is incredibly important that someone be aware of the presence of a trigger, but awareness isn't enough. I was aware my client had a trigger and I forgot, so awareness only goes so far. An even better plan is to have processes in place to mitigate the risk of a trigger being dropped.
My VAR or consultant will remember
Maybe they will remember, but I wouldn't want to bank on it.
Case in point: the situation that triggered me to write this article has had 3 instances already where the trigger was forgotten about, by the very people that many clients believe *will remember* everything about their environment.
- The most recent instance was my forgetfulness. I had forgotten about the SQL trigger entirely and it didn't even occur to me to check for any triggers before the client's VAR performed their tax updates.
- The VAR installing the service pack is the same one who wrote the original trigger. However, the consultant who wrote the trigger wasn't the consultant doing the service pack install, so of course, there weren't any flags raised like "Hey, don't forget to re-script that trigger!".
- During the client's last upgrade, the only way I found out about the trigger in the first place was at go live, when the client noticed their cheques were missing descriptions. No one had documented the trigger in the first place and the client wasn't aware of it, so we scrambled at the time to figure out what was "wrong" with the cheque descriptions.
A better recommendation
What is the impact of a trigger going missing in the Dynamics GP environment? Is it a minor annoyance or a significant data loss? I've seen triggers in use that could fall into both categories.
How can I mitigate this kind of risk? There are probably multiple ways and I won't even get into "don't have triggers in the first place!" as one of them.
Documentation
In a perfect world, every customization is documented and in the same perfect world, every consultant or VAR installing a tax update or service pack will re-read the documentation before proceeding to remind themselves of what exactly is unique about this client's environment. Reality check: this doesn't happen.
Even in situations where the same consultant is working for the organization every time work is done, in between site visits, they are likely working with dozens of other clients and yes, details get lost, and clients get confused with each other. Documentation is helpful but only if people read it.
Pre-installation processes
Another way to mitigate the risk would be to have processes in place where someone runs scripts to check for certain types of things like triggers before installing service packs, in case there is a need to re-apply them. Triggers are a tough one. They aren't visible, like other Dynamics GP "custom" elements such as modified forms, reports, VBA, or add-ins. Triggers aren't visible simply by looking in the database. Many other SQL objects are still not obvious but more visible than triggers may be, depending on the naming convention.
Having a process in place that includes a pre-install script to look for custom SQL objects might be a good place to start. However, if there are some, and admins or installers are unaware of them, what should be done at that point when it comes time to start an installation of some sort? Is it worth taking the time to just script them out or is it better to see what they do first?
SQL job to alert if dropped
The third option is what I chose to put in place and it worked like a charm. I created a SQL job that runs once a day looking specifically for the SQL trigger. If the trigger exists, the job succeeds and no alert is fired. If the trigger doesn't exist, the job fails and I receive an email.
Early this morning, I received an email. Oops.
SQL Job Setup
My SQL job is fairly simple, and this is the script I've put in to run nightly:
DECLARE @DoesTriggerExist int
SELECT @DoesTriggerExist = COUNT(*)
FROM sys.triggers
WHERE object_id = OBJECT_ID(N'MYTRIGGERNAME')
IF (@DoesTriggerExist = 0)
/* throw error because the trigger is missing if <> 0, that means trigger exists and nothing needs to occur */
RAISERROR ('MYTRIGGERNAME is missing. Please re-create the trigger',11,1);I'm simply querying the sys.triggers table for my specific object name and then raising an error if the results are "zero" triggers found. I am expecting a result of 1 each time of course.
The SQL job registers a failure and depending on how the alerts are set up, I can have it be an event viewer message, or an email or I could even be writing failures to a status table and reporting off that if I wish. In my case, the alerts trigger an email.
The actual alert to the client contains some more details like where to find the script to recreate the trigger and who to contact for assistance, etc. This job runs nightly so that any issue would be caught first thing the next day.
All in all, it was a very simple solution that worked like a charm!
