January 13, 2009 at 4:34 pm
I have 6 tables tbl1,tbl2-6
I need to write a program to determine if any changes were made to these tables.
Option 1, I can write a trigger that sends an msmq message on any change.
Option 2, I can use some sort of hash calculation to determine if it has changed last time I looked.
The goal is to notify users running a program via udp messages if a table changes, which these tables wouldn't change frequently. However when they do the program they run will receive the message and rebuild the generic lists that their static class contain.
Any suggestions, ideas etc...
January 13, 2009 at 5:14 pm
what do you mean by table change - is it data in tables or table structure?
January 13, 2009 at 7:40 pm
Writing a trigger would be the best way to go. Since triggers do not have that much over head in 2005 like it had in 2000, triggers shouldnt be an issue. You can handle the trigger in different ways. You can as well write the changes to another table, and let the application poll it from there. It can poll once every sec if you want it to.
-Roy
January 14, 2009 at 6:05 am
I'm assuming you're referring to data changes in the table...
Triggers are probably going to be the simplest to implement. Keep it really simple and just set a flag somewhere that shows the data as being changed, don't try to launch update services from the trigger itself.
Assuming access to the tables is controlled through code, say stored procedures, you could modify the procs so that it also sets a change flag somewhere in the system so that you know that data has changed.
Place a datetime column on the table, make it part of any updates or inserts (you'd need to use logical deletes, not physical) and then select against that to see if the data has changed.
Those are the ones that come immediately to mind. I still lean towards a trigger.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 7:36 am
so the best way to do it is to set a trigger for afterupdate / afterinsert / afterdelete
populate another table with :
changedtablename, changeddate, changedvaluestring
Have my service process run polling against this table for changes.
Wouldn't it be possible to have an afterinsert on this table to call a stored procedure that calls a clr function that messages the changed data to all the clients. Or does the clr not allow us to use those communication tools that .net readily has access to with normal programming?
January 14, 2009 at 8:38 am
foxjazz (1/14/2009)
so the best way to do it is to set a trigger for afterupdate / afterinsert / afterdeletepopulate another table with :
changedtablename, changeddate, changedvaluestring
Have my service process run polling against this table for changes.
Wouldn't it be possible to have an afterinsert on this table to call a stored procedure that calls a clr function that messages the changed data to all the clients. Or does the clr not allow us to use those communication tools that .net readily has access to with normal programming?
This is one of those situations where, because you can do something doesn't mean you should do it.
Yes, you can have a trigger that calls CLR procedures that make a call out to your code to reload the cached data or however you're storing. Depending on the frequency and volume of the updates against the original tables, this could work. Or, it could be major performance bottle neck because these CLR calls will affect each and every transaction against the tables in question, slowing them down. As you slow down these transactions, you get longer blocks due to locking. As each process blocks longer, more processes are needed to manage the wait states, the server begins to suffer, you start to experience deadlocks, etc., etc.
I shy away from using triggers at all because of some of the uses they're put to, such as this. You'll need to very carefully evaluate if you should perform these actions through a trigger.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 8:43 am
Ok, instead of giving me problems, how about providing a solution.
These tables are not (data) are not changed frequently, and the data is cached on the clients software.
When the data does change, however, I need to notify the clients of this change. I can setup udp listeners on the clients to take these notifications (no problem).
Why would setting up a trigger to tell a program to send messages to the clients about the change, so they can update their cache be so difficult?
January 14, 2009 at 8:51 am
Because depending on the type of trigger if it fails it could roll back the transaction. Also you are making one more step to a normal transaction. (ie an Insert or an Update needs to do one more step.)
If this is a static table, Why not poll the table once every minute to check if there are any changes?
-Roy
January 14, 2009 at 8:56 am
foxjazz (1/14/2009)
Ok, instead of giving me problems, how about providing a solution.These tables are not (data) are not changed frequently, and the data is cached on the clients software.
When the data does change, however, I need to notify the clients of this change. I can setup udp listeners on the clients to take these notifications (no problem).
Why would setting up a trigger to tell a program to send messages to the clients about the change, so they can update their cache be so difficult?
I wasn't aware I was "giving you problems." I thought I was discussing your questions. I was honestly attempting to provide information.
As I said in my previous post, you can do this. It will work.
There are however, things you need to take into consideration. I say that you need to take them into consideration rather than my taking them into consideration because I'm not on your site performing work for you. I'm voluntarily answering posts in a free forum. You're not paying for the service and I'm not get paid for it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 9:06 am
It sounds like you should look into Service Broker for what you want. Making something happen outside the database from a trigger is usually a baaaad idea. Now that DBMail uses Service Broker and is asynchronous you can do that, but otherwise you could be in trouble.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 14, 2009 at 9:09 am
foxjazz (1/14/2009)
Ok, instead of giving me problems, how about providing a solution.
This is not the first time that you've criticised the free help that you're getting here. If you want a solution to your exact specs, with no questions asked, hire someone. We're doing this in our free time.
We're also going to tell you if what you want to do is potentially risky. If someone mentioned driving off a cliff, would you advise him not to, or give him directions to the nearest cliff?
Why would setting up a trigger to tell a program to send messages to the clients about the change, so they can update their cache be so difficult?
It's not difficult. It's simple code to write. It, however, is not recommended.
Triggers should be as short and as fast as possible. The longer the trigger, the longer the locks are held, the more blocking and possible deadlocks you will have. Also, the trigger runs within the scope of the transaction that fired it. If the trigger code fails (eg if there's a network glitch) the changes to the table will be rolled back.
Generally the suggested way to do this is to have the trigger write to a table, logging that some set of data was changed, and having a job or scheduled app running regularly that checks that table and does whatever's necessary.
Or, have the trigger write into a service broker queue and have procedure that fires when the message arrives do the notiifications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2009 at 9:27 am
I'll throw another idea out. You said this data is cached in the application, perhaps the application should be rewritten to use query notification so that when a change occurs in one of the tables, the application can handle the update.
Check it out in BOL.
January 14, 2009 at 9:35 am
I have used the message queue before and it works very effectively.
It's too bad however I can't use it to send a udp message to listening clients.
Maybe 2008 has a feature that will allow me to do just that. I don't know.
January 14, 2009 at 9:38 am
I have triggers that call a stored procedure that sends a message using service broker.
However this is all contained within one system, and transaction could not fail because messages are non-transactional reactional.
However even doing this doesn't give me a way to send a udp message from a sql server function, which is what I am really after.
January 14, 2009 at 9:39 am
foxjazz (1/14/2009)
It's too bad however I can't use it to send a udp message to listening clients.
Why not? Have the service broker queue's activation procedure be a CLR procedure. It it's registered as External Access and all the requirements are satisfied, there's no reason why you shouldn't be able to send UDP.
Same as you were considering doing in the trigger, just asynchronous and in a separate transaction
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply