Before i get started i wanted to know what am i getting myself into,
Some specs:
Nav 4.0 with SP1
SQL server 2005 (developer)
I need a table from a separate program (Not Navision) it is stored in SQL 2005, when a record is added there i want it to run a trigger and add it to the Navision Database, from there i will run another trigger to perform some actions (create a customer, vendor and then some invoices). I think it might be straight forward but before i start diggin i want to know if there will be any major things i should watch out for.
Don’t mess around with SQL Server table triggers in your NAV database, that will only get you in trouble. I would probably create a separate SQL Server table for the data, and program a process internal to NAV, that polls that table every so many seconds, and processes information from that table into NAV. Probably this process would be implemented using Navision Application Server.
The important part is to find a way to run the NAV specific business process, and you can only guarantee that if you have a process in NAV process the data.
Well, of course you could use the “SQL site” table triger to write information into the NAV table, but it is not possible to run the code from the NAV site trigger, as OnInsert() - that’s actually completely different things …
Thus, to run the business logic in NAV - e.g. the OnInsert Trigger (NAV) - you have to establish a process inside NAV, as DenSter proposes.
What maybe could help in this process is this:
You could create a SQL View in the NAV database which refers to your “other” SQL table. Then you could create a “Linked Table” in NAV for this View. Maybe this table then could be used for processing the “business logic” in NAV!?
It is possible to write back to a linked table in Navision that links to a view on SQL server but this is dependent on the view on SQL server. The view on SQL server must be updatable. Some views on SQL server can’t be updated - e.g. a view containing a SUM function can’t be updated. You can run into some permissions issues if the view on SQL server shows data from another database which can be a bit tricky to solve.
It’s possible to write to a view, but you have to give the user db_owner or do other tricks. I did get one to work, but since then I’ve discovered ADO automation, which is much smoother in terms of controlling the data, and it is much easier to write to an external data source. I don’t think I would use views and linked tables again for writing purposes.
If the result must be in an external data source, you could use a linked table to read information and ADO to write the result back. Reading using ADO is really fast too by the way. There’s an example in the mibuso download section.
If you can replicate all NAV business logic in an SP then by all means go ahead, but the risk of data corruption will be yours. It is a LOT easier to write a NAS process that does it using standard NAV functionality than it would be to write an SP.