Does MySQL permit callbacks in C such that when a

Question

Does MySQL permit callbacks in C such that when a change happens in the database, like an insert, that is performed by a different program or by the user at the command line, I can be notified?

I am guessing that it doesn't, because mysqlclient is a library, not a running thread. But I may as well ask.

Answer 1

Create a trigger like so.

DELIMITER $$

CREATE TRIGGER ad_mytable_each AFTER DELETE ON MyTable FOR EACH ROW
BEGIN
  #write code that trigger After delete (hence the "ad_" prefix)
  #For table MyTable (The _MyTable_ middle)
  #On each row that gets inserted (_each suffix)
  #
  #You can see the old delete values by accesing the "old" virtual table.
  INSERT INTO log VALUES (old.id, 'MyTable', old.field1, old.field2, now());

END$$

DELIMITER ;

There are triggers for INSERT, DELETE, UPDATE
And they can fire BEFORE or AFTER the action.
The trigger BEFORE the action can cancel the action by forcing an error, like so.

CREATE TRIGGER bd_mytable_each BEFORE DELETE ON MyTable FOR EACH ROW
BEGIN
  #write code that trigger Before delete (hence the "db_" prefix)
  declare DoError Boolean; 

  SET DoError = 0;

  IF old.id = 1 THEN SET DoError = 1; END IF; 

  IF (DoError = 1) THEN SELECT * FROM Table_that_does_not_exist_to_force_error;
  #seriously this example is in the manual.

END$$

DELIMITER ;

This will prevent deletion of record 1.

A before UPDATE Trigger can even change the values updated.

CREATE TRIGGER bu_mytable_each BEFORE UPDATE ON MyTable FOR EACH ROW
BEGIN
  IF new.text = 'Doon sucks' THEN SET new.text = 'Doon rules';
END$$

DELIMITER ;

Hope you'll be Trigger happy.

Answer 2

You can use triggers combined with UDFs (user defined functions) so that the corresponding action on the database executes a trigger that calls a C/C++ function.

Just consider that this mechanism runs your code inside the mysql server process, not in the client side.

Answer 3

Well you could attach a trigger to user defined function, and have it call an external program, that would then notify your code..

http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-B-5-1-10

Answer 4

MySQL's triggers allow you to hook into insert/update/delete queries and do something additional. You could log them in a separate table, for example.