Thanks for the sounding board. It should be pretty easy to transition my current trigger code to the data directive.
We have written a two way sync in our E9 environment between Epicor Customer/CustCnt tables with our Dynamics CRM Account/Contacts tables. We have done so using SQL CLR triggers on the Customer and CustCnt tables and Plugins on the Dynamics side to detect Inserts/Updates/Deletes. To link entities, we are using a ShortChar field to track the Dynamics CRM account and contact GUIDs. Enter E10. Now the Customer table is actually two tables, Customer and Customer_UD. Trying to implement the SQL CLR trigger, I need data from both tables. Does anyone know which table gets written to first? Is there actually any guarantee one gets written before the other?
Tim
Can you make use the the view dbo.Customer? That contains the UD_SysRowID as ForeignSysRowID.
Rob Bucek
Production Control Manager
D&S Manufacturing
301 E. Main St.
Black River Falls, WI 54615
715-284-5376 Ext. 311
Mobile: 715-896-3119
Visit our newly redesigned Website at www.dsmfg.com
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, September 12, 2016 2:37 PM
To: vantage@yahoogroups.com
Subject: [Vantage] E10 SQL Table interaction
We have written a two way sync in our E9 environment between Epicor Customer/CustCnt tables with our Dynamics CRM Account/Contacts tables. We have done so using SQL CLR triggers on the Customer and CustCnt tables and Plugins on the Dynamics side to detect Inserts/Updates/Deletes. To link entities, we are using a ShortChar field to track the Dynamics CRM account and contact GUIDs. Enter E10. Now the Customer table is actually two tables, Customer and Customer_UD. Trying to implement the SQL CLR trigger, I need data from both tables. Does anyone know which table gets written to first? Is there actually any guarantee one gets written before the other?
Tim
I actually tried that but the trigger did not fire when an update or insert occurred on the Customer table. I used the Instead of Insert trigger but it was unclear to me when the trigger would fire and I was unable to get it to fire in my testing.
You may have better luck using the data directive tool, one, it’s more supported, and from there you can code anything you want really.
Rob Bucek
Production Control Manager
D&S Manufacturing
301 E. Main St.
Black River Falls, WI 54615
715-284-5376 Ext. 311
Mobile: 715-896-3119
Visit our newly redesigned Website at www.dsmfg.com
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, September 12, 2016 3:15 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: E10 SQL Table interaction
I actually tried that but the trigger did not fire when an update or insert occurred on the Customer table. I used the Instead of Insert trigger but it was unclear to me when the trigger would fire and I was unable to get it to fire in my testing.
That's a good idea. I will test around with that.
Why a trigger vs data directive?
Joshua Giese
Technology Solutions : CTO
Direct Phone:
920.593.8299
Office Phone:
920.437.6400 x342
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, September 12, 2016 15:15
To: vantage@yahoogroups.com
Subject: [Vantage] Re: E10 SQL Table interaction
I actually tried that but the trigger did not fire when an update or insert occurred on the Customer table. I used the Instead of Insert trigger but it was unclear to me when the trigger would fire and I was unable to get it to fire in my testing.
The core of my design is using a Microsoft Message Queue. I wanted to minimize the time either Dynamics CRM or E9 would spend doing any heavy work, so the Dynamics CRM Plugin and the E9 SQL triggers simply acknowledge the event and then write what happened to a Message Queue. I then have separate windows services that wake up when the corresponding Message Queue gets data and does the heavy work. At the time, I was much more comfortable with .Net and C# than I was with ABL so I didn't even consider using a Data Directive. With E10 now being C#, the Data Directive is really the better choice over the SQL CLR Trigger.
It’s pretty cool what you can do with them these days, as either internal or external assembly. Essentially it’s a trigger in it’s own right.
Rob Bucek
Production Control Manager
D&S Manufacturing
301 E. Main St.
Black River Falls, WI 54615
715-284-5376 Ext. 311
Mobile: 715-896-3119
Visit our newly redesigned Website at www.dsmfg.com
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Tuesday, September 13, 2016 7:02 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: E10 SQL Table interaction
The core of my design is using a Microsoft Message Queue. I wanted to minimize the time either Dynamics CRM or E9 would spend doing any heavy work, so the Dynamics CRM Plugin and the E9 SQL triggers simply acknowledge the event and then write what happened to a Message Queue. I then have separate windows services that wake up when the corresponding Message Queue gets data and does the heavy work. At the time, I was much more comfortable with .Net and C# than I was with ABL so I didn't even consider using a Data Directive. With E10 now being C#, the Data Directive is really the better choice over the SQL CLR Trigger.