Detect if a table has changed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Detect if a table has changed

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-22 : 10:53:06

I am looking for ideas / direction.

What I need to achieve is some form of notification to a .NET application if a specific MS SQL table is changed.

e.g.:

User 1
Starts application which loads a combo box from table a (client list)

User 2
Updates table a (client list) by adding a new client

What I need is a notification to be sent to all running instances of the application (on the network) to notify the application to reload table a into the combo. How can I know what clients on the network are currently running the specific appln? Would I need a general broadcast - seems excessive.

Something like the following might be possible, but how could I achieve it?

1 Appln loads on client
2 Registers itself with sql by creating an entry in an sql table to request notification if table "x" changes
3 Need something running on server to monitor for changes to table "x"
4 On identifying a change (how?) send a message to all clients as registered in 2 above.

Sounds easy enough but I have no idea of where to start!!

Another, perhaps better, idea might be to hold a timestamp in another table stating when table "x" was last updated. Then each time I would normally load the client table first check to see if the timestamp had been updated. If it has changed then load the table into the combo otherwise continue with what is already loaded.

i.e.

table: timestamps
fields: tablename, timestamp

1 App starts
2 get timestamp for "client list" table from timestamps table
3 load client list
4 each time I require the "client list" check if timestamp has changed
if it has reload

What if "client list" changed between the time I read the timestamp and the time I read the "client list"?

In SPs that update the client list I include code to update the timestamp :

...
update client list
....
... then
UPDATE [dbo].[tblUpdatesTimeStamp]
SET
[tablename] = [tablename] -- forces an update of the field timestamp
WHERE tablename='client_list'

Any suggestions or comments?

Declan

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 12:05:10

i would do this with triggers. do trigers after insert, delete and update in which you fill
another table with messasge which table changed (MessageTable).
then you can send a mail to user. that requires scheduling. i don't know if the mail can be sent instantly.
it can if it's in a trigger, but that not advisable.
another option is that you have a timer in your app that checks the MessageTable for updates and if there
are tell the user to reload.

Go with the flow & have fun! Else fight the flow

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-22 : 12:48:59

Is the important part that the combo box lists stay in sync with the data or users need notification when data changes so they can participate in the work flow?

Surf On Dude!

Kristen
Test

22859 Posts

Posted - 2004-09-22 : 12:58:03

Store the ComboBox in the APPLICATION object [and refresh it there when the data changes]? (Or doesn't something like that exist in ASP.NET any more?)

Kristen

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-22 : 14:02:47

Yes the idea is to keep the combobox in sysn with the data on the server - without having to reload frequently.
-dw

-dw

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-22 : 14:03:55

It is a windows form appln not asp.net.

-dw

Kristen
Test

22859 Posts

Posted - 2004-09-22 : 14:21:52

In that case you'll have to store some sort of "flag" value in a table somewhere that you query routinely - perhaps as part of something else - from the client application, and when that "flag" is set then also query to get a fresh list for the combo, and cache it until it changes again.

The "flag" needs to be a datetime, or an integer that is incremented each time there is a change, so that each client application can say "is the FLAG more recent/ bigger than last time I checked"

It might be sufficient to have an "ChangedDate" column in the table - thus you could do "SELECT MAX(ChangeDate) FROM MyTable" and only pull the whole data set when the MAX(ChangeDate) is more recent that the last time you checked

Kristen

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-22 : 14:26:34

A call to the following function tells me whether I need to reload:

Private Function CheckTimeStamp(Optional ByVal sTableName As String = "Clients") As Boolean

'Default initial value
Static tTime As Byte() = {0, 0, 0, 0, 0, 0, 0, 0}
Dim bChanged As Boolean = False

Dim _clsTimeStamp As db_TimeStamp
_clsTimeStamp = db_TimeStamp.GetInstance

'Get time stamp for last update to Clients table
_clsTimeStamp.GetTimeStamp(sTableName)

Try
If IsNothing(_clsTimeStamp.Exception) Then

'Store time last updated in byte array
Dim tLastUpdated As Byte()
tLastUpdated = _clsTimeStamp.db_TimeStampData.Tables("db_TimeStamp").Rows(0).Item("TimeStamp")

Dim i As Integer

'Check each byte and set bChanged if any are different
For i = 0 To 7
If (tLastUpdated(i) <> tTime(i)) Then
'Difference found
bChanged = True
Exit For
End If
Next

If Not bChanged Then
Console.WriteLine("Not Changed")
Else
Console.WriteLine("Changed")
'Changed so now store last time changed
tTime = tLastUpdated
End If
Else
MessageBox.Show(_clsTimeStamp.Exception, "Check Time Stamp", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Check Time Stamp", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

_clsTimeStamp = Nothing

Return bChanged

End Function

The table db_TimeStamp has 1 entry for each table of interest. Fields are:

TableName
TimeStamp

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 14:37:55

would something like this work. you'll have to check and debug the code...



Private Shared myTimer As New System.Windows.Forms.Timer()
		
Public Shared Sub Main()
	' Adds the event and the event handler for the method that will
	' process the timer event to the timer.
	AddHandler myTimer.Tick, AddressOf TimerEventProcessor
	
	' Sets the timer interval to 60 seconds.
	myTimer.Interval = 60000
	myTimer.Start()
	
End Sub

' This is the method to run when the timer is raised.
Private Shared Sub TimerEventProcessor(myObject As Object, _
                                   myEventArgs As EventArgs)
	if CheckTimeStamp = True
		ShowMessageToUser or RefreshData
	End If
End Sub

Go with the flow & have fun! Else fight the flow

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-22 : 14:48:05

Thanks, spirit1 and Kristen for youur input.

I think I will go with my function to test the timestamp table in order to decide if I need to load the clients. i.e. before loading any other tables that reference the clients table.

This might be preferable than checking on a timer which could generate needless calls to the db.

Thanks for your input - it got me thinking (no mean feat!!!)

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 14:52:25

ok, but how will you check your table in intervals otherwise than in timer??? i know of no other way...
you say before loading the clients. and what if the data changes after the client gets what he needs??

Go with the flow & have fun! Else fight the flow

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-22 : 15:02:44

Yes I understand your point. However, in this case, just before a job is loaded I will check that I have the current clients list (at that time) this means I have the latest relevant to the current job.

When a user creates a new job or selects another I will check that the "current" client list is loaded and if not I will load it, then continue to load the selected job or create a new one as requested by the user.

I was not critical of your suggestion. I just think, knowing my application, that in this particular case a timer is not required. I do use a timer, similarly to your suggestion, in another area which periodically checks for input to a web hosted database.

Thanks.

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 15:07:58

i didn't take it as criticism

, just trying to look at it from different views.

Go with the flow & have fun! Else fight the flow

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-22 : 15:19:07

OK - thanks. Sorry if I misread you.

-dw

Kristen
Test

22859 Posts

Posted - 2004-09-23 : 04:20:15

What about an event on the DropDown button of the ComboBox (do they have buttons, I can never remember which type is which) which checks for new rows, and if there are any does a just-in-time refresh of the comboBox.

Depends how slow your connection is to the database I suppose.

You could also throw away the combo box and replace it with a "find" box. The user types in something that they know of the value they seek, you query the database for matches, and insert the value (if only one hit) or display the results for the user to pick one.

Works nicely where tables have gazzillions of rows where the old ComboBox starts to get a liittttlllleeeee sluggish!

Kristen

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-23 : 05:05:01

Just-in-time as the combo opens sounds good. I'll do some tests to see what the response is like.

-dw

Kristen
Test

22859 Posts

Posted - 2004-09-23 : 10:30:34

If its too slow do the database check just-before the user clicks the button <vbg>

Kristen

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-09-23 : 10:43:24

It's hard enough to try and control what the users actually do never mind trying to predict when they might do it!

-dw