Link to home
Start Free TrialLog in
Avatar of jnikodym
jnikodym

asked on

Dynamics GP - SQL Trigger

I need to create a SQL trigger on the SOP10200 table to track the date and time a new line item was added to a sales order in Dynamics GP.  I would like to write out the Sales order number, Line item, price, and time stamp to a custom table.  Any help with the coding on this trigger would be appreciated.  Thank you
Avatar of Abdulmalek_Hamsho
Abdulmalek_Hamsho
Flag of United Arab Emirates image

It's not recommended to apply SQL triggers on any of GP tables. You can use Dex_TS column instead.
Avatar of jnikodym
jnikodym

ASKER

That column gives you the last modified date, not the date the record was written to the table.
You need to use Dexterity Table Triggers then.
Avatar of Victoria Yudin
Abdulmalek_Hamsho,

Can you give some reasoning for why this is not recommended?  That may very well be the case, but I think it would be helpful to have some backup for your statement so that the reasons can be evaluated to see if they apply in this case specifically.
Basically all i am looking for in the end is a a custom table that lays out the date a line item was added to a sales order along with the price of that item.  I would want the trigger to run for new records as well as updates to existing records.
Can you explain what you are trying to show us from this link?
Why using SQL Triggers is not recommended with Dynamics GP, and that SQL Triggers interfere with Dynamics GP processes.

If you insist on applying SQL Triggers on Dynamics GP DB, read this article:

http://dynamicsgpland.blogspot.com/2009/10/using-and-debugging-sql-triggers-with.html


Again, it's not recommended.
ASKER CERTIFIED SOLUTION
Avatar of Victoria Yudin
Victoria Yudin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Victoria, that was my thinking as well.  I don't want to modify any existing GP tables.  I want to write to a new custom table data from a GP table.
Well, if the above case does not include the case where the INSERT SQL Trigger updates non-GP tables does not mean it is not troublesome. My suggestion is to do one of the followings:

1- Search the Internet for a case where the INSERT Trigger updates non-GP table, and see others experience in this, then apply it on your Test environment and test thoroughly.
2- Read how to make a Table trigger in Dexterity, it's easy. (refere to the Dexterity Manuals).