SQL database triggers are an interesting, powerful, and potentially dangerous tool. On the one hand, they offer the appealing ability to execute a SQL script automatically whenever a change is made to a database table. The trigger is fired immediately by SQL server, and frees you from having to separately monitor database tables to try and detect changes to data.

On the other hand, triggers can be difficult to implement, debug, and manage, and can cause unintended consequences. Since I don't use them very often, I don't claim to be a trigger guru, but my interpretation is that SQL Server does not provide very good tools for managing, diagnosing, and debugging triggers. And when you are dealing with a moderately complex application like Dynamics GP, triggers can get you into trouble very quickly--for several different reasons. You have to really understand not only all of the tables, records, and underlying data, but you also need to very clearly understand the sequence of events that occur in those tables if you want to successfully implement a trigger.

I was recently asked to customize GP to automatically populate a single field on a SOP transaction. The client wanted a SOP User Defined field filled out automatically based on the customer and address ID assigned to the order. But they wanted this field to be populated without requiring additional user intervention, and they needed it populated even for orders that were imported from their e-commerce web site.

After weighing the pros and cons of different approaches, I chose to use triggers. I reasoned that triggers would be the most reliable, as they could most easily populate the field regardless of how the order was entered in GP, and a trigger could be fired without requiring any user intervention. One field, right? How hard could that be?

I think that triggers turned out to be a good choice, but I ran into a few surprising issues that required me to learn a few things about GP, and also how to debug triggers on SQL Server.

It turns out that modifying the SOP User Defined fields table was trickier than I thought. I chose to put a trigger on the SOP10100 table, because that table would contain the customer ID and address ID that I needed to lookup my user defined value that was stored in the Customer Master Address File (RM00102). But, of course, it wasn't that easy. There are dozens of things that occur in the database when GP creates a new SOP transaction, so I had to reverse engineer each step of that process to resolve a few errors that were occuring. Here is the trigger that I ended up creating.

-- =============================================
-- Author: Steve Endow
-- Create date: 7/22/2009
-- Description: On…