Fog Creek Software
Discussion Board




Database Triggers - Good, Bad, Ugly?

(Apologies to m for blatantly stealing his subject)

Consider a situation where I've got a Windows GUI application that allows the user to have multiple views of the underlying data. A plain and simple C++/MFC MDI application.

If I'm using database triggers to do work at the SQL Server level, how do I communicate related database changes back to the GUI?

For example: My user changes a record in one window, and we commit that change to the database which causes a related table to be changed by virtue of a trigger. If another MDI child window is showing data from that related table, how do I ensure it's refreshed so that it now shows the updated data?

I figure I can either:

(1) Not bother. When each MDI child window gains focus you rerun the query that generates the data that it's displaying.

(2) Don't use triggers. Do all the work that you'd otherwise do in triggers within your data model / business objects. Publish events to yourself indicating that various data elements have been updated, and allow your MDI windows to subscribe to these events to note that a refresh is required.

I've used (1) in the past, but it seems a bit... icky. (2) isn't any nicer.

Can someone suggest a (3)?

Andrew Lighten
Tuesday, September 02, 2003

I can't see how you can avoid scenario 2, with or without triggers.

The way I see it, you have a group of observers waiting to be notified of changes in the resources they're observing. Whether you make your changes via triggers or not, you'll always have to notify the observers that they need to refresh.

--
"Suravye ninto manshima taishite (Peace favor your sword)" (Shienaran salute)
"Life is a dream from which we all must wake before we can dream again" (Amys, Aiel Wise One)

Paulo Caetano
Tuesday, September 02, 2003

True, you need to have observers.

A 3rd solution (not good at all) would be to poll your DB for update using let's say looking up at last update DateTime stamp :(

Neither of them are good. You will have to make a weighted judgement depending on your DB size, no of times GUI updates, no of views etc. etc. etc.

The One You Loved (TOYL)
Tuesday, September 02, 2003

Id do a combination of both.
Use triggers on the database as you are currently doing, but also store a record of the record id somewhere locally and note that its being changed. 
I assume that the record you update has the id of the related tables that are changed by the triggers?  if so you can return that as part of the update sequence (or grab it once the update has been performed depending on the database you are using).
Once you have that setup you can basically choose how often you update the second window..All of your open windows can be polling your list of altered records locally as often as you like and can redo their own particular query if necessary.

FullNameRequired
Tuesday, September 02, 2003

How about:

1) Create an open views table

2) When a view is opened, it is also written in the table with a timestamp.

3) Create a stored proceudre that, given a table name, checks the open views table.  If the table is there, its timestamp is updated.

4) Have call triggers call the open views update procedure.

5) For your application create an observer that monitors the open views table on a regular basis.  When an update takes place, the relevant window is told to refresh.

Ged Byrne
Tuesday, September 02, 2003

I hope your CDocument derived class is what is updating the db, in which case it should then just call UpdateAllViews. I see no reason for anything more complicated than this unless the data in the database is represented in several documents.


Tuesday, September 02, 2003

This might be a good approach:

Maybe you can use the xp_cmdshell stored procedure in MS SQL Server to issue a net send command. This way your tigger code detects an update/change of a field or table and can then issue

exec master.dbo.xp_cmdshell ' .............' and issuing a net send command from the shell to a designated port. You then listen on that port from your clients;

net send 192.168.0.1,4321 "This is your wakeup call"

This message could be taiolored to contain the table that was updated or whatever.

HTH

Patrik
Tuesday, September 02, 2003

Look at the Observer pattern and the Mdel-View-Controller pattern.

Paul
Tuesday, September 02, 2003

Here is  an example SP using net send to communicate

http://www.planet-source-code.com/vb/scripts/ShowCodeAsText.asp?txtCodeId=530&lngWId=5

HTH

Patrik
Tuesday, September 02, 2003

Sorry disregard my previous post with the URL.
Too quick cutting and pasting; here is the URL i meant
to send:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

Patrik
Tuesday, September 02, 2003

An important related consideration is, is this a multi-user environment? If so, can users work on each others data? If so, do you need one user's changes to be immediately reflected on another user's desktop? Or is there changing "static" data that needs to be reflected on the user's desktop (e.g. real time stock price information)?

These would all influence your decision. I personally can recommend a simple database/tcpip event driven model, controlled by as described earlier in this thread. This would of course depend on your database server, and whether it provides this kind of thing. Sybase's Open/Server is a good example of one that works well, but I'm sure all the leading products have similar tools.

In my experience, the simpler your solution can be, the better. If you can avoid heavyweight solutions like CORBA, you reduce the things to look at when things go wrong in your production environment. Especially when it comes to apps running on PC's, where its difficult to control the strange things users do.

Chris Welsh
Tuesday, September 02, 2003

A simple idea:

1. Use triggers
2. Have the trigger update a counter
3. Poll the database by checking the counter.

If you structure it well, the query in 3 can be very simple and fast:

  select counter from Notification where notification_id = ?

I think this is the fastest polling that you can do; to do better, the DB has to somehow notify your application.

Peter Breton
Tuesday, September 02, 2003

Thanks to everyone for lots of constructive suggestions... but...

I guess what I'm looking for is a way to find out what the side effects are, rather than the fact that a change has occurred. Simplying notifying the database that a chance has occurred is easy. The hard bit -- and the one that I'm grappling with -- is knowing what the change is.

I've always been taught that one of the fundamental properties of good code is that every fact is known in exactly one place. I can't see how I can (a) use database triggers to handle data update side effects, and (b) have all my MDI child windows to stay up-to-date without breaking this rule.

Even if the database can come back (via xp_cmdshell or some other server-side notification mechanism) the problem I have is how this mechanism knows what has changed.

Hmmm... actually, now that I think about it... can someone with more intimate knowledge of SQL Server tell me if this will work?

(I'm going to be using MSDE for a project; I haven't used it before, but I know/knew Sybase System 10 backwards, so it won't take long to ramp up.)

(1) Create a private temporary table when I connect to the server. The table has a single column that holds the name of a table that's been updated

(2) Every trigger on every table writes the name of its table into this temporary table.

(3) When I've finished my SQL and my 'commit' has succeeded, I query this temporary table and I get a unique list of the tables that changed. I issue events in my C++ application to notify everything which tables have been updated, then I clear the contents of this temporary table ready for the next run.

Andrew Lighten
Tuesday, September 02, 2003

Write an extended stored proc that talks to your application. Whatever you do, though, don't take the "shell out and run NET SEND" advice. That's just awful!

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, September 02, 2003

Brad,

Thanks for pointing out extended SP's. Wasn't around in MS SQL server when I used it last, back in 1999 or so.

Patrik
Wednesday, September 03, 2003

*  Recent Topics

*  Fog Creek Home