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 insert of SOP transaction, updates
-- the SOP UDF table (SOP10106) to store the 'End User ID'
-- value in the SOP UDF Text 3 field.
-- The End User ID value is stored in the UDF1 field of
-- the customer address record in RM10102.
--
-- Rev 1.0: 7/22/2009
--
-- Rev 1.0f: 7/23/2009
-- Corrected error that would cause SOP get next number to fail (SET @ENDUSERID = '')
-- Corrected error during Order transfer to Invoice: Trigger would insert SOP10106
-- record before GP did, causing GP to get dup index error
--
-- =============================================
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[ctrStoreEndUserIDSOP10100]'))
BEGIN
DROP TRIGGER ctrStoreEndUserIDSOP10100;
END
GO
CREATE TRIGGER ctrStoreEndUserIDSOP10100
ON SOP10100
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @SOPTYPE AS smallint
DECLARE @SOPNUMBE AS char(21)
DECLARE @ORIGTYPE AS smallint
DECLARE @ORIGNUMB AS char(21)
DECLARE @CUSTNMBR AS char(15)
DECLARE @ADRSCODE AS char(15)
DECLARE @ENDUSERID AS char(15)
DECLARE @UDFEXISTS AS smallint
DECLARE @ORIGUDFEXISTS AS smallint
SET @SOPTYPE = 0
SET @SOPNUMBE = ''
SET @ORIGTYPE = 0
SET @ORIGNUMB = ''
SET @CUSTNMBR = ''
SET @ADRSCODE = ''
SET @ENDUSERID = ''
--Retrieve the values from the inserted / updated record
SELECT @SOPTYPE = SOPTYPE, @SOPNUMBE = SOPNUMBE, @ORIGTYPE = ORIGTYPE, @ORIGNUMB = ORIGNUMB, @CUSTNMBR = CUSTNMBR, @ADRSCODE = PRSTADCD FROM inserted
--Do not fire if customer or address ID are empty
IF @CUSTNMBR <> '' AND @ADRSCODE <> ''
BEGIN
--Query the end user ID from the customer Ship To Address ID
SELECT @ENDUSERID = RTRIM(USERDEF1) FROM RM00102 WHERE CUSTNMBR = @CUSTNMBR AND ADRSCODE = @ADRSCODE
--Check to see if the UDF record exists for this SOP transaction
SELECT @UDFEXISTS = COUNT(*) FROM SOP10106 WHERE SOPTYPE = @SOPTYPE AND SOPNUMBE = @SOPNUMBE
--Also check to see if the UDF record exists for an originating transaction (in case of transfer to invoice)
SELECT @ORIGUDFEXISTS = COUNT(*) FROM SOP10106 WHERE SOPTYPE = @ORIGTYPE AND SOPNUMBE = @ORIGNUMB
IF @UDFEXISTS = 1
BEGIN
--Update the UDF Text 3 field with the End User ID based on the Ship To Address ID
UPDATE SOP10106 SET USRDEF03 = @ENDUSERID WHERE SOPTYPE = @SOPTYPE AND SOPNUMBE = @SOPNUMBE
END
ELSE
BEGIN
IF @ORIGUDFEXISTS = 0
BEGIN
--Insert a new record into the SOP UDF table, including the end user ID in UDF Text 3
INSERT INTO SOP10106
(SOPTYPE, SOPNUMBE, USRDAT01, USRDAT02, USRTAB01, USRTAB09, USRTAB03, USERDEF1, USERDEF2, USRDEF03, USRDEF04, USRDEF05, COMMENT_1, COMMENT_2, COMMENT_3, COMMENT_4, CMMTTEXT)
VALUES (@SOPTYPE, @SOPNUMBE, '1900.01.01', '1900.01.01', '', '', '', '', '', @ENDUSERID, '', '', '', '', '', '', '')
END
END
END
END
GO
After the trigger was installed, when transferring an order to an invoice, GP would throw a 'duplicate index' error. And when trying to create a new sales order, gp complained that it was unable to get the next document number. When we disabled the trigger, GP worked fine, so it was clear that the trigger code was causing the errors.
The errors were not very specific, so I had little to go on. After some digging, I found that I could debug the trigger code using Visual Studio. This allowed me step through each line of code in my trigger, much like a .NET program, and discover the cause of the errors.
To debug triggers on SQL 2005, you will need Visual Studio 2005 Professional. I'm only going to describe a basic process for debugging T-SQL triggers, but here is an MSDN web page that describes many options and caveats for debugging T-SQL and CLR with SQL 2005. There are several ways to debug T-SQL with Visual Studio, but this is one method that I stumbled on based on the limited instructions I could find.
1. Create a new stored procedure in your database that performs a very simple operation that will cause the trigger to fire. In my case, I had an INSERT, UPDATE trigger, so I created the following stored proc in TWO:
CREATE Procedure DebugTrigger
AS
UPDATE SOP10100 SET ORIGTYPE = ORIGTYPE WHERE SOPNUMBE = 'ORDST2230'
GO
2. Open Visual Studio 2005 Professional and create a new "Database Project".
3. When asked to choose a database reference, either add a new connection, or select an existing connection for your GP database.
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 insert of SOP transaction, updates
-- the SOP UDF table (SOP10106) to store the 'End User ID'
-- value in the SOP UDF Text 3 field.
-- The End User ID value is stored in the UDF1 field of
-- the customer address record in RM10102.
--
-- Rev 1.0: 7/22/2009
--
-- Rev 1.0f: 7/23/2009
-- Corrected error that would cause SOP get next number to fail (SET @ENDUSERID = '')
-- Corrected error during Order transfer to Invoice: Trigger would insert SOP10106
-- record before GP did, causing GP to get dup index error
--
-- =============================================
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[ctrStoreEndUserIDSOP10100]'))
BEGIN
DROP TRIGGER ctrStoreEndUserIDSOP10100;
END
GO
CREATE TRIGGER ctrStoreEndUserIDSOP10100
ON SOP10100
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @SOPTYPE AS smallint
DECLARE @SOPNUMBE AS char(21)
DECLARE @ORIGTYPE AS smallint
DECLARE @ORIGNUMB AS char(21)
DECLARE @CUSTNMBR AS char(15)
DECLARE @ADRSCODE AS char(15)
DECLARE @ENDUSERID AS char(15)
DECLARE @UDFEXISTS AS smallint
DECLARE @ORIGUDFEXISTS AS smallint
SET @SOPTYPE = 0
SET @SOPNUMBE = ''
SET @ORIGTYPE = 0
SET @ORIGNUMB = ''
SET @CUSTNMBR = ''
SET @ADRSCODE = ''
SET @ENDUSERID = ''
--Retrieve the values from the inserted / updated record
SELECT @SOPTYPE = SOPTYPE, @SOPNUMBE = SOPNUMBE, @ORIGTYPE = ORIGTYPE, @ORIGNUMB = ORIGNUMB, @CUSTNMBR = CUSTNMBR, @ADRSCODE = PRSTADCD FROM inserted
--Do not fire if customer or address ID are empty
IF @CUSTNMBR <> '' AND @ADRSCODE <> ''
BEGIN
--Query the end user ID from the customer Ship To Address ID
SELECT @ENDUSERID = RTRIM(USERDEF1) FROM RM00102 WHERE CUSTNMBR = @CUSTNMBR AND ADRSCODE = @ADRSCODE
--Check to see if the UDF record exists for this SOP transaction
SELECT @UDFEXISTS = COUNT(*) FROM SOP10106 WHERE SOPTYPE = @SOPTYPE AND SOPNUMBE = @SOPNUMBE
--Also check to see if the UDF record exists for an originating transaction (in case of transfer to invoice)
SELECT @ORIGUDFEXISTS = COUNT(*) FROM SOP10106 WHERE SOPTYPE = @ORIGTYPE AND SOPNUMBE = @ORIGNUMB
IF @UDFEXISTS = 1
BEGIN
--Update the UDF Text 3 field with the End User ID based on the Ship To Address ID
UPDATE SOP10106 SET USRDEF03 = @ENDUSERID WHERE SOPTYPE = @SOPTYPE AND SOPNUMBE = @SOPNUMBE
END
ELSE
BEGIN
IF @ORIGUDFEXISTS = 0
BEGIN
--Insert a new record into the SOP UDF table, including the end user ID in UDF Text 3
INSERT INTO SOP10106
(SOPTYPE, SOPNUMBE, USRDAT01, USRDAT02, USRTAB01, USRTAB09, USRTAB03, USERDEF1, USERDEF2, USRDEF03, USRDEF04, USRDEF05, COMMENT_1, COMMENT_2, COMMENT_3, COMMENT_4, CMMTTEXT)
VALUES (@SOPTYPE, @SOPNUMBE, '1900.01.01', '1900.01.01', '', '', '', '', '', @ENDUSERID, '', '', '', '', '', '', '')
END
END
END
END
GO
After the trigger was installed, when transferring an order to an invoice, GP would throw a 'duplicate index' error. And when trying to create a new sales order, gp complained that it was unable to get the next document number. When we disabled the trigger, GP worked fine, so it was clear that the trigger code was causing the errors.
The errors were not very specific, so I had little to go on. After some digging, I found that I could debug the trigger code using Visual Studio. This allowed me step through each line of code in my trigger, much like a .NET program, and discover the cause of the errors.
To debug triggers on SQL 2005, you will need Visual Studio 2005 Professional. I'm only going to describe a basic process for debugging T-SQL triggers, but here is an MSDN web page that describes many options and caveats for debugging T-SQL and CLR with SQL 2005. There are several ways to debug T-SQL with Visual Studio, but this is one method that I stumbled on based on the limited instructions I could find.
1. Create a new stored procedure in your database that performs a very simple operation that will cause the trigger to fire. In my case, I had an INSERT, UPDATE trigger, so I created the following stored proc in TWO:
CREATE Procedure DebugTrigger
AS
UPDATE SOP10100 SET ORIGTYPE = ORIGTYPE WHERE SOPNUMBE = 'ORDST2230'
GO
2. Open Visual Studio 2005 Professional and create a new "Database Project".
3. When asked to choose a database reference, either add a new connection, or select an existing connection for your GP database.
4. Now that you have created a new project, expand your database connection on the left, and expand Stored Procedures. It will take a while and may warn you that there are alot of procedures to display (just click on Yes).
5. Right mouse click on your test stored procedure and select "Step Into Stored Procedure"
6. A new tab should open displaying your stored procedure code in debug mode, ready to step into the first line of your procedure.
7. Press F11 to Step Into the procedure line
8. A new tab will open showing your trigger code in debug mode
9. You can now press F11 to step through each line of your trigger
10. Note that the Locals window will allow you to easily see the values of all of the trigger parameters
In the case of my trigger, it turns out that I had two bugs. The first bug caused the insert of the SOP10106 record to fail (my code was trying to insert a NULL value). Because my trigger was on the SOP10100 table, this caused GP's update of the SOPNUMBE field to fail, resulting in the next number error. This 'cascade' effect of triggers is just one of the problems that can make them potentially dangerous, and difficult to debug in production environments. If your trigger fails, it may cause a train wreck as it causes other higher level operations to fail.
The second problem I had was that if an order is transferred to an invoice, GP would attempt to create a new record in SOP10106 for the invoice, but my trigger had already created one as soon as the SOP10100 record was updated. This caused GP to receive a duplicate index error. So my trigger was fighting with GP to insert the record. So in this case, understanding the exact sequence of events that occur under different circumstances is required to ensure that the trigger does not interfere with GP's normal operations.
1 comment:
Thanks for the post. This was a huge help in explaining and configuring so triggers can be debugged. Now I'm struggling with trying to see the values in the inserted table.
Post a Comment