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
Microsoft DynamicsMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Abdulmalek_Hamsho

8/22/2022 - Mon
Abdulmalek_Hamsho

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

ASKER
That column gives you the last modified date, not the date the record was written to the table.
Abdulmalek_Hamsho

You need to use Dexterity Table Triggers then.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
jnikodym

ASKER
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.
Abdulmalek_Hamsho

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jnikodym

ASKER
Can you explain what you are trying to show us from this link?
Abdulmalek_Hamsho

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
Victoria Yudin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jnikodym

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Abdulmalek_Hamsho

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).