Raise an event from MySQL and handle it from VB.NET (or something similar)?

Question

I'm working with MySQL 5.1.39 and Visual Studio 2008 and connecting both with MySQL Connector Net 6.1.2.

What I'd like to do is once a MySqlConnection object is created, be able to handle the "event raised" when a field in a specific row in a given table is updated.

I mean, when that value in that table has been manually changed or modified from any other application, I'd like to receive a signal in my opened VB.NET application. Until now, I do it from opened VB.NET application checking that table every X seconds, but I wonder if it could be done in a better way.

Many thanks for your attention and time.

Answer 1

My first thought was to use a database trigger to trigger some sort of notification: message through email, MOM or anything else. Googling didn't turn much up though. I found one approach based on notification through locks: linky. Could be a sane approach...

Oh, and in that blog post they also talk about MySQL UDFs which lets you execute arbitary code when triggers fire. Apparently there is libs to various languages. There is also a duplicate question here on stackoverflow. Cheers

Answer 2

I never worked with that but I think "TRIGGER" could be what you're looking for.

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

Answer 3

Ideally, there is the SIGNAL construct, which you can use to field SQL logic errors, but that is not available until MySQL 5.5. It would be best to upgrade to 5.5, if at all possible.

EDIT: There isn't really a good solution for this before 5.5. The TRIGGER works for getting the updates, but not for sending them outside of the database. Be careful, though, as this doesn't work if you're updating through FOREIGN KEY actions, such as CASCADE or UPDATE, as TRIGGERs are not called for these actions. So watch out for that.

DELIMITER $$
CREATE TRIGGER my_trigger_name AFTER UPDATE ON my_table_name
FOR EACH ROW BEGIN
    CALL my_on_update_procedure(NEW.entry_name, NEW.whatever_else)
END $$

DELIMITER ; 

What my_on_update_procedure does is up to you. Your solution is probably the best bet for 5.1.39 (I would not recommend locking due to scalability issues), but 5.5 would give you the SIGNAL construct, which is exactly what you want (so upgrade!).