How to know from client side if table was updated

Question

I am building a C# Winforms client connected to a MySQL database. The client will be accessible from multiple users and computers. I need a way for all the clients to somehow be notified whenever another one makes a change to database contents so to refresh in the client.

Since when a user opens a form that shows database contents, the contents are fetched at runtime. So that is not the issue. If an update is made the user will see it.
My problem is if a user has a form of database content open and another user changes database content.

I thought of setting up a timer of some sort and every minute get all the database contents that the user is currently viewing. But that is very time consuming and not efficient at all. Another thought was every minute check for updates but I do not know how to implement that.

Any suggestions?

Is there a way to get check for updates from client side?
Is there any other way to perform this?

possible duplicate of [How can I tell when a MySQL table was last updated?](http://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated) — Kami, Sep 29 '14 at 08:46
@Kami I actually searched and that answer did not appear in the search results... I'll test it and see if it works — John Demetriou, Sep 29 '14 at 08:50
The linked question might help you identify if a table is changed, but tacking on individual rows does not appear to be possible. — Kami, Sep 29 '14 at 08:53
Yeah what I want is for individual records not the table itself. If new data was added or if any data was edited — John Demetriou, Sep 29 '14 at 08:59

score 0 · Accepted Answer · answered Sep 29 '14 at 09:21

What you could is change your table so it stores the timestamp whenever it is updated. For example

CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

The above code store the current timestamp to a new row and changes it the time edited whenever a row is edited. This way you can get the data, sort it (order by) in descending mode and get the top record. That way you get the last updated record in your table. If you want to know for the whole database then you have to compare the last updated record for each of your tables and get the most recent one.

How to know from client side if table was updated

1 Answers1