Linked table query

hav

Hi All,
I have two databases on my SQL Server instance.
--> WMDATA : third party database
--> Demo Database NAV (6-0) : NAV 2009 demo database
The WMDATA database contains a table WMTracking.
I have created a view 'CRONUS International Ltd_$WMTracking' using SQL Server 2005 in NAV demo database. The definition of the view is as follows:
CREATE VIEW [CRONUS International Ltd_$WMTracking]
AS SELECT * FROM WMDATA.dbo.WMTracking
Note that the source table of the view is WMTracking table of WMDATA db.

I then created a table WMTracking in NAV having LinkedObject property set to Yes so that the table is linked to the view.

When i run a thrid party application, tracking transactions are generated and stored in WMTracking table of WMDATA db. This in turn also generates the record in WMTracking view and then WMTracking table of NAV demo db.

The problem i face is:-
I want to do some processing when a record is inserted in WMTracking table in NAV. For this i have written some code in OnInsert() trigger of WMTracking linked table in NAV and what i noticed is that the trigger is not executed when a new record is inserted although by the third party application.

The question is why does the trigger not executed when a record is inserted in WMTracking linked table?

Please help.

Regards,
Hemant

fredp1

You pretty much answer your question...

The Navision triggers that you wrote is executed by the Nav application.
The trigger is stored in Nav, not the SQL db.

You will either need to write a SQL trigger or a trigger in your third party app.

stryk

Hi!

Instead of the NAV Trigger, maybe you could create a small function which periodically checks (Timer, NAS, Job Scheduler) for new entries in this "Linked Object" Table to start the processing then?
This should be easy; maybe when the record is inserted into the WMDATA database a flsg (e.g. "Processed") is set to FALSE, then in NAV you could filter on those records for processing ...

Regards,
Jörg

jlandeen

There needs to be a an active NAV instance to actually executate any of the business logic that you want it too. As discussed here it's possible to have a report/codeunit run via job queue or on some timer that executes the logic that you want it to.

Another option would be to use SQL to call NAV directly (assumes you are running a full blown NAV 2009 RTC environment with an application server). Basically the idea is to have your WMData database make calls to NAV when events happen - and use the new Web Service functionality of NAV 2009 to execute your logic.

1) Build a Page or Codeunit in NAV that exposes a function with your business logic in it
2) Update the WMTracking table in the WMData databse with an insert trigger that invokes some managed .Net code
3) in this .Net trigger - call the NAV web service and perform any required tasks

Good example for connecting to NAV as a web service: https://community.dynamics.com/blogs/navdavidroys/comments/31769.aspx

hav

Hi,
Thanks for your support.

I have decided to use a job queue to schedule a codeunit at regular interval. The codeunit will execute the required business logic.

Regards,
Hemant