Listening SQL insert

Cem_KaraerCem_Karaer Member Posts: 281
edited 2011-06-28 in SQL General
Hello,

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

Regards.
Cem Karaer @ Pargesoft
Dynamics NAV Developer since 2005

Comments

  • ara3nara3n Member Posts: 9,255
    you can create sql triggers. write your code in tsql.
    Could you provide more info, on what you are trying to accomplish?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Cem_KaraerCem_Karaer Member Posts: 281
    I mistakenly posted this question to the wrong topic. Please move it to the right one. I am so sorry.
    Cem Karaer @ Pargesoft
    Dynamics NAV Developer since 2005
  • Cem_KaraerCem_Karaer Member Posts: 281
    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 @ Pargesoft
    Dynamics NAV Developer since 2005
  • avataravatar Member Posts: 5
    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_KaraerCem_Karaer Member Posts: 281
    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 @ Pargesoft
    Dynamics NAV Developer since 2005
  • Cem_KaraerCem_Karaer Member Posts: 281
    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 @ Pargesoft
    Dynamics NAV Developer since 2005
  • avataravatar Member Posts: 5
    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_KaraerCem_Karaer Member Posts: 281
    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 @ Pargesoft
    Dynamics NAV Developer since 2005
  • avataravatar Member Posts: 5
    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.
  • rhpntrhpnt Member Posts: 688
    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_KaraerCem_Karaer Member Posts: 281
    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 @ Pargesoft
    Dynamics NAV Developer since 2005
  • rhpntrhpnt Member Posts: 688
    cemkaraer wrote:
    Because SQL is used for insertion, NAV OnInsert trigger cannot be run.

    You use NAS for that purpose (Job Queue).
  • Cem_KaraerCem_Karaer Member Posts: 281
    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 @ Pargesoft
    Dynamics NAV Developer since 2005
  • rhpntrhpnt Member Posts: 688
    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_KaraerCem_Karaer Member Posts: 281
    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.
    Cem Karaer @ Pargesoft
    Dynamics NAV Developer since 2005
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from 'NAV Tips & Tricks' forum to 'SQL General' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.