mibuso.com

Microsoft Business Solutions online community
It is currently Fri Jul 25, 2014 10:35 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 17 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Listening SQL insert
PostPosted: Mon Jun 27, 2011 9:00 pm 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
Hello,

Is there any automation server with triggers that will catch up any insert/update/delete actions done in SQL Server?

Regards.

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Mon Jun 27, 2011 9:07 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 9027
Location: 3rd rock from sun
Country: United States (us)
you can create sql triggers. write your code in tsql.
Could you provide more info, on what you are trying to accomplish?

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Mon Jun 27, 2011 9:09 pm 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
I mistakenly posted this question to the wrong topic. Please move it to the right one. I am so sorry.

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Mon Jun 27, 2011 9:14 pm 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
Hello,

An external application will make an insert to a NAV table. NAV should process that inserted record as soon as possible.

The application logs the incoming phone calls. As soon as a new phone call is logged, NAV should be aware of this, search the incoming phone number throughout the customer phone numbers. If it finds any matching customer then it should open up the customer's card (or something similar) to inform the salesperson about who is calling.

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 9:00 am 
Offline

Joined: Mon Sep 22, 2008 3:48 pm
Posts: 4
Probably you can search DB and build a Navision link to the customer's card from the external application more efficiently as handling insert triggers in SQL.


Top
 Profile E-mail  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 9:13 am 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
avatar wrote:
Probably you can search DB and build a Navision link to the customer's card from the external application more efficiently as handling insert triggers in SQL.

Hello avatar,

I didn't understand what you meant.

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 9:50 am 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
Well I changed my mind so I decided to listen the Windows Event Log. I created a table trigger for INSERT in the NAV table. When an insert occurs, this trigger writes an entry to the Windows Event Log of the SERVER. So if it is impossible to listen SQL inserts, is it possible to listen Event Log entries of the server?

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 10:10 am 
Offline

Joined: Mon Sep 22, 2008 3:48 pm
Posts: 4
Hello cemkaraer,

sorry for my poor English. :oops:

I mean the external application (you mentioned it) already has access to the Navision DB. I assumed it because it can write the phone number of the calling customer to the DB. This application could also search the customer tables to find the matching customer entry and build a Navision link like this:
navision://client/run?servername=your-navision-server%26database=your_navision_db%26company=your-conpanyname%26target=Form%205050%26view=SORTING(Field1)%26position=Field1=0(kontakt-no)%26servertype=MSSQL

We developed a similar application, written in c#, to show caller information to our Helpdesk.
But, maybe this is not what you are looking for.


Top
 Profile E-mail  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 10:32 am 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
Hello avatar,

Thank you for your reply. The main purpose of this development is to consolidate the functions in one place. I wanted to use classic client for salespersons. The salesperson will see the caller's identity instantly when he answers the phone. The information about the customer will include its name, address, credit limit ect. In this screen he should be able to select items and make sales orders.

Because the functionality will be heavy in the NAV side, the centeral working place must be in NAV environment. NAV must be informed of the current phone calls so that it prepares the necessary information.

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 10:51 am 
Offline

Joined: Mon Sep 22, 2008 3:48 pm
Posts: 4
OK. I will try to clarify. Here are the main points of my approach:

1.) Call is coming in, and it is detected by an external application (let's call it phone_to_nav)
2.) phone_to_nav searches the DB for customers who match the phone number (maybe just a SQL select statement to the customer table)
3.) If match is found, then phone_to_nav builds a "navision://" URL and calls it. That will open Navision Client if it were not already open, and open the Contact Form with the Customer Contact Information matching the caller’s phone number.

Of course you have to take care of a lot of cases not listen above (phone number not found, multiple matches, etc.).

The external application (our phone_to_nav) is just taking the phone number, search for matching customer and call/start Navision to show customer information. Reached this point, your sales person can just edit information because it is working with Navision as it were started manually.

Hope this help you.


Top
 Profile E-mail  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 11:25 am 
Offline

Joined: Mon Jul 28, 2008 2:37 pm
Posts: 674
Location: AT
Country: Austria (at)
cemkaraer wrote:
Hello avatar,

Thank you for your reply. The main purpose of this development is to consolidate the functions in one place. I wanted to use classic client for salespersons. The salesperson will see the caller's identity instantly when he answers the phone. The information about the customer will include its name, address, credit limit ect. In this screen he should be able to select items and make sales orders.

Because the functionality will be heavy in the NAV side, the centeral working place must be in NAV environment. NAV must be informed of the current phone calls so that it prepares the necessary information.


One solution is actually quite simple. When the record is inserted into the NAV table (a dedicated I hope) the code in the OnInsert trigger finds the corresponding contact/customer/vendor number (using the telephone No.) and writes it into the appropriate fields of that table/record. A special form checks the entries in the previous table using code in the OnTimer trigger. If a new record with the right parameter(s) is added (new call) the data of the contact/customer/vendor is showed (card opened).


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 11:54 am 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
avatar wrote:
3.) If match is found, then phone_to_nav builds a "navision://" URL and calls it. That will open Navision Client if it were not already open, and open the Contact Form with the Customer Contact Information matching the caller’s phone number.


It would be a good idea if we could run the third party telephony application on salespersons' computers but we cannot. The telephony application is run on the server. Also having the third-party application customized is not so simple. We hardly persuaded them to make insertion to another table different than their internal database.

rhpnt wrote:
When the record is inserted into the NAV table (a dedicated I hope) the code in the OnInsert trigger finds the corresponding contact/customer/vendor number (using the telephone No.) and writes it into the appropriate fields of that table/record.


Because SQL is used for insertion, NAV OnInsert trigger cannot be run. But using OnTimer trigger in a form seems the only viable solution if NAV cannot listen any action outside. But using OnTimer will be very very unefficient.

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 12:13 pm 
Offline

Joined: Mon Jul 28, 2008 2:37 pm
Posts: 674
Location: AT
Country: Austria (at)
cemkaraer wrote:
Because SQL is used for insertion, NAV OnInsert trigger cannot be run.


You use NAS for that purpose (Job Queue).


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 12:16 pm 
Offline

Joined: Mon Jun 12, 2006 10:18 am
Posts: 239
Location: Istanbul
Country: Turkey (tr)
rhpnt wrote:
You use NAS for that purpose (Job Queue).


Even if we use NAS or not, the OnTimer trigger will grossly slow down the system.

_________________
Cem Karaer
http://www.pargesoft.com.tr


Top
 Profile  
 
 Post subject: Re: Listening SQL insert
PostPosted: Tue Jun 28, 2011 2:22 pm 
Offline

Joined: Mon Jul 28, 2008 2:37 pm
Posts: 674
Location: AT
Country: Austria (at)
cemkaraer wrote:
Even if we use NAS or not, the OnTimer trigger will grossly slow down the system.


NAS is used to "watch" over the insert table - even if it is set up to fire every second it won't slow down the whole system only the PC on which it is running.

The same goes for the form with the timer - again not the whole system maybe the PC(s) on which it is activated. Any solution will have some impact on the client PC's because there will be some service running in the background - or else you'll never know on which PC to open the customer card.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 17 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: