Questions Run an SQL Script at Time of Save

Posted by: @jt

Is there a way thru Acumatica customization projects to run an SQL script dynamically? Probably the best way to explain this is the use case scenario.

We are using Acumatica to create new stock items. But we have a 2nd SQL database (not-Acumatica) that needs to know when a new stock item has been created and copy the data over. Ideally upon saving the new stock item in Acumatica an SQL script is run that pushes the necessary info over to the other SQL database. Is this possible within the bounds of the built in Acumatica customization projects? Or would it require more in depth customization?

@jt, given that the second DB is a non-Acumatica DB, you will not have (direct) access to it from the framework.

I can think of a couple of workarounds:

1. The trigger suggested by @carl-brooks 
Acumatica does not encourage the use of SQL objects i.e. SQL views, stored procedures, triggers, etc. for 2 main reasons: i) your application is no longer DB-agnostic. ii) your application would no longer be certifiable by Acumatica.
Having said that, it's my impression that you are looking to do this as a one-time-only requirement. In which case, the SQL trigger is certainly worth looking into.

2. As a second alternative, if you have access to the non-Acumatica DB via an API, you could create an Acumatica processing page that would get executed periodically in order to sync the data.
Pros: you would be able to manage it from the framework.
Cons: the sync would not occur immediately. It would depend on your process recurrence.