Listening SQL insert

Cem_Karaer

Hello,

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

Regards.

ara3n

you can create sql triggers. write your code in tsql.
Could you provide more info, on what you are trying to accomplish?

Cem_Karaer

I mistakenly posted this question to the wrong topic. Please move it to the right one. I am so sorry.

Cem_Karaer

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.

avatar

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.

Cem_Karaer

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

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?

avatar

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.

Cem_Karaer

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.

avatar

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.

rhpnt

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).

Cem_Karaer

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.

rhpnt

cemkaraer wrote:

Because SQL is used for insertion, NAV OnInsert trigger cannot be run.

You use NAS for that purpose (Job Queue).

Cem_Karaer

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.

rhpnt

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.

Cem_Karaer

rhpnt wrote:

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.

You are right. I have no other way to achieve the request. The code run in the OnTimer trigger must be as efficient as possible. Thanks.

kriki

[Topic moved from 'NAV Tips & Tricks' forum to 'SQL General' forum]