Do you know when a SQL trigger is dropped?

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 had posted about the issue that led to this particular trigger in the first place. The short version is 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

If you were to search for information about SQL triggers, chances are you’ll find more articles about why you should avoid them, or be cautious at the very least, than you might about the wonders of them!

In the Dynamics GP world, depending on what tables you are creating the triggers on, you may find that when you apply a service pack or perform a version upgrade, those SQL objects may be dropped. Tim Wappat wrote a post a few years ago about this very thing, with some tips on scripting out triggers before upgrades.

Personally, 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.

1. The most recent instance was my own 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.
2. 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!”.
3. 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 your 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 you 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 you have the same consultant working on your site every time you need some work done, in between site visits, they are likely working at 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 you run scripts to check for certain types of things like triggers prior to installing service packs, in case you need to re-apply them. Triggers are a tough one. They aren’t visible, like other Dynamics GP “custom” elements are 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 you *find* some, and are unaware of them, what do you do at that point when you are presumably ready to start an installation of some sort? Do you take the time to just script them out or do you have 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 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 you set up your alerts, you can have it be an event viewer message, an email or you could even be writing failures to a status table and reporting off that if you wish. In my case, the alerts trigger an email.

The actual alert at 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!