On changes client notification

Hi everyone,

I'm working with Delphi 4 C/S.

I could have posted this question on the Delphi side, but, as I think this is more related to MS SQL and it should be a general procedure, I did it here.

The question:

I've got an operator application running against MS SQL Server, where multiple operators can be changing tables that other operators can be using at the same time. Those that are using the updated tables cannot see the updates, but might need to use them.

What is the mechanism to make the Server to notify the clients applications that something has changed on the database, so that the client application can refresh its data and work with the most recent data?

Clues:
The kind of solution I'm thinking of, is a mechanism similar to the Interbase triggers/POST_EVENT solution. With this, for each table, I write a trigger that posts an event to the application when this "registers interest" in that event.

I know that triggers are available on MS SQL 6.5, but I don't arrive to understand the whole thing. If can send me an example, it will be wonderful.

Thanks a lot.

Felixin
LVL 2
FelixinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Victor SpiridonovCommented:
There is no standard feature ,that will 'broadcast' change notification from MS SQL server to clients. You will have to implement this functionality  as a client function. For example you can have an additional table in your database, that will have two columns : table name and last update date and  create  an update trigger on the tables you want to track, that will be resetting value in this table. Then you can make you client query this table at period intervals and update data when update time changes.
0
FelixinAuthor Commented:
Hi SPIRIDONOV,

I'm rejecting this answer not because it is not good, but because it will make the client application to slow down as it will have to periodically query that table, and to leave the question open.

In fact I have already thought that this could be the solution, but I need somenthing faster and asyncronous.

However, if nobody gives me a better solution, I won't mind giving you the points, as the solution is easy to implement and, surely, can work.

Thanks

Félix
0
chigrikCommented:
To send a message to all users on need will update their recordsets, you may to use "net send" command. xp_cmdshell is extended stored procedure that allows your to executes a given command string as an operating-system command. Read about xp_cmdshell in SQL Server Books online.
Ensure the "Messenger" service is started (Control Panel - Services - Messenger - Auto).
Note.You must run WinPopup.exe utility to receive messages on Windows 95 computers.

It's example:

CREATE TABLE tbTest (
        id int IDENTITY (1, 1) NOT NULL ,
        Text varchar (50) NULL
)
GO

ALTER TABLE tbTest
       ADD CONSTRAINT PK_tbTest PRIMARY KEY  NONCLUSTERED
        (
                id
        )  
GO

CREATE TRIGGER Example_trig ON tbTest
FOR INSERT, UPDATE, DELETE
AS
  EXEC master..xp_cmdshell 'net send * "Please, refresh to apply last changes"'
GO

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

FelixinAuthor Commented:
Even if it might work, the requirement for me is that it should be made automatically.

Thanks, chiqrik.

Felixin
0
jadekrafCommented:
I think spiridonov has a good solution.  However you could also use a stored procedure to update the table.  Have the stored procedured make sure the record has not changed since the user retrieved the record.  If it is the same version then apply changes - otherwise raise an error.  The error can be handled multiple ways in your client.  You can trash their work and reload or compare fields and show differences and allow some kind of merge.
0
FelixinAuthor Commented:
Jadekraf,

What you're telling me is kind of messy and time consuming. I cannot asume the time to develop this.

(Excuse if I seem too unpolite).

Thanks for your answer, anyway.

Felixin
0
wqwCommented:
You should move to 3-tier if you want to accomplish this kind of update notification. The way we do it in VB is to stream updates through the middle tier from where we raise events.

Basicly we have trans DLLs under MTS that define events like RowUpdated(NewData as Variant). We share common ActiveX EXE on MTS that is a single threaded server (so that global data is visible from all functions) and does nothing but propagating events to all loaded trans DLLs which in turn propage events to all Client components that are connected to the trans DLL this way: Dim WithEvent oTrans As CTrans

We realize that this is NOT SACLEABLE design and try to minimize its use. Hope that COM+ will make things easier.

</wqw>
0
jadekrafCommented:
If you are worried about making sure users have the latest version of a record you are obviously in a multiuser environment and expect frequent changes to records.  If so, then before you write any changes to the database you have to insure that records have not been changed since the client began their change.  The ONLY way to do that is to wrap your update in a transaction.  Within that transaction you must determine if the record in the database is the same one that the client had when they began editing.  If it is the same record you allow the change, otherwise you can handle appropriately.  If you do not perform your updates in a manner similar to this you will cannot guarantee that you will not overwrite someone else's changes.
0
FelixinAuthor Commented:
Hi everyone,

For SPIRIDONOV:

To be fair, I have just tested your porposal. With Delphi it hasn't been such a complicated thing and seems to work fine. I only have some flicker in the cursor while the application makes its refreshing task.

As your suggestion requires a minimun effort to be implemented (I needed to do it fast) and to be coherent with what I think how this should work, please answer again this question and yours will be the points.

Thanks to everyone.
0
Victor SpiridonovCommented:
Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FelixinAuthor Commented:
With regards

Felixin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.