From the usability standpoint, workflow in Microsoft Dynamics GP can be used for maximizing efficiency by involving the right people at the right time. Microsoft Dynamics GP includes six workflows - and others can be built by Microsoft Dynamics partners. But from the developer's standpoint, it might make your software development project a little more challenging.

Around 70 tables of Microsoft Dynamics GP got a new field used for workflow which cannot be seen from Dexterity. The field is DEX_ROW_TS. The value of this Dex Row TimeStamp field is changed whenever the record is modified. I know that it looks guilelessly, but the DEX_ROW_ID field existed for ‘centuries’ and it didn’t bother anyone (and was helpful for all T-SQL code inside projects). However, the change was made.

The challenge is that the DEX_ROW_TS field is updated whenever the record is updated through the AFTER UPDATE trigger. So, if your product has the same type of trigger on the table which has this timestamp field it will be executed twice. Once for the original update, and once for the update of DEX_ROW_TS field. If your trigger is not there to update redundant or calculated data in other tables - but it updates the same table on which it was triggered - the loop of the trigger execution is insight.

There are a few ways to avoid the challenge caused by this approach of updating DEX_ROW_TS. Due to the compatibility of Microsoft Dynamics GP with SQL Server 2000, the solutions below are valid for SQL Server 2000 and newer versions:

  • Check the nested level of your trigger and execute the logic only if it’s not nested (see more about TRIGGER_NESTLEVEL() in SQL Server Books Online)
  • Check if DEX_ROW_TS column is updated by wrapping the trigger code into IF (NOT UPDATE (DEX_ROW_TS)) 
  • In one particular case we had to be sure that our trigger executed after the Microsoft Dynamics GP trigger which updates field DEX_ROW_TS - and that can be managed by using sp_settriggerorder

Based on the logic that should be achieved, different solutions might be suitable for overcoming the problem that has been discussed here. Any other ideas for avoiding this problem are more than welcome.