Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: How to monitor a database table from an external application

    Hi all,
    I'm wtiting a multi-user DB application using MSSQL. How can I monitor a specific table from my application so that if a user updates the table, the underlying code be able to notify the user and do something.
    (The app. is written in delphi, I believe the problem is not bound to a specific PLanguage).

    Thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    7
    Two things come to mind

    1) Table UPDATE trigger
    2) Extended Stored Procedure (see xp_hello in the Books Online, think sockets, or your pipe of choice)

  3. #3
    Join Date
    Dec 2003
    Posts
    5
    Thanks Makallus.
    A question about the first option you suggested.
    What about the application iteself? How can I use DB triggers to inform the second user? Is my application able to communicate with the trigger? In other words, what code can I use in the associated Script for the trigger in order to notify my application? (I mean T-SQL)
    Last edited by IranInPrison; 12-26-03 at 18:01.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    When would the second user need to be notified ?

  5. #5
    Join Date
    Dec 2003
    Posts
    5
    Hi,
    The second user must be notified as soon as any insertion, update, or deletion occurs, of course just by other users. The user being notified is just abled to view the records.

    Thanks
    Originally posted by rnealejr
    When would the second user need to be notified ?

  6. #6
    Join Date
    Dec 2003
    Posts
    7
    To inform a user as soon as the INSERT/UPDATE/DELETE occurs you use 2) which is the Extended Stored Procedure. Using an ESP in the trigger you can fire any external application or DLL which communicates, say on a port to any particular user based on your escallation. You would have to provide more details about the notification schema for me to provide details. Please see the Books Online for "xp_hello", with a little imagination you can fire off a DLL in the trigger which communicates directly with an application.

  7. #7
    Join Date
    Dec 2003
    Posts
    5
    Thanks Makallus for your notice.
    The application is a medical one. We have two doctors and one secretary. The secretary forms a queue of patients for each doctor. The queue is just a DB table. The seretary selects each patient form queue and passes the patient medical file to the related doctor for modification electronically using socket programming I've used in my delphi application(This is the scheme preferred by the doctor to get rid of the need to enter patient medical history key by him.)
    Each doctor needs to view his own queue of patients and sometimes he is allowed to open the patient's medical file by selecting a specific patient from this queue.
    But the problem arises when the seretary inserts or deletes a patient from the queue. What happens then if the form containing the queue is already open on the doctor's system? The doctor views the old data.

    Thanks.

    Originally posted by Makallus
    To inform a user as soon as the INSERT/UPDATE/DELETE occurs you use 2) which is the Extended Stored Procedure. Using an ESP in the trigger you can fire any external application or DLL which communicates, say on a port to any particular user based on your escallation. You would have to provide more details about the notification schema for me to provide details. Please see the Books Online for "xp_hello", with a little imagination you can fire off a DLL in the trigger which communicates directly with an application.

  8. #8
    Join Date
    Dec 2003
    Posts
    7
    Using the ESP you can call an application or a DLL which in turn sends a message to the client, to , say, refresh the view, or update a local (memory) queue.

    In your app, it sounds like the secretary is already a socket connected to each doctor client. If so then if the secretary updates the database it can also send a message to the doctor clients to update the view -- much simpler.

    The jist is that you can use messaging from the DATABASE using an Extended Stored Procedure and the Trigger can fire this message thus providing realtime notification.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •