MySQL is free & robust, When we were developing a program for some voip calling implemenatation and we had a requirement to call some external programm when there is a change in one of our mysql table. And MySQL triggers are provided for the same purpose. Now it is easy.
Now that we know, how to define your own functions and call them from MySQL events, we need to write our logic in a PHP program by following the interface provided by MySQL and we are done.
Wait a minute. That is already done by somebody. They have made a library of UDFs. One of them, LIB_MYSQLUDF_SYS does exactly what we want.
DOWNLOAD LIB_MYSQLUDF_SYS from below URL
OR
Direct Download
It allows you to call an external program from MySQL procedure/trigger. Once you download and untar the files, you'll get an install.sh. Just need to run it but, make sure you have gcc and make before that. The install script copies the library shared object file to /usr/lib. In some 64 bit systems it may be /usr/lib64. Also you need to check MySQL plugin_dir property which points to the directory where the .so file should be placed.
way to use plugin_dir
in my.cnf under
[mysqld]
plugin_dir=/usr/lib
Once the UDF is installed successfully, we can use 2 functions to call an external program -
1. sys_exec : Returns the exit code of the external program.
2. sys_eval : Returns the output from the external program.
Here is a code snippet which show an example:
DELIMITER @@
CREATE TRIGGER Trigger_one
AFTER INSERT ON Table_one
FOR EACH ROW
BEGIN
DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd=CONCAT('php /var/www/html/asterisk/passvalue.php');
SET result = sys_exec(cmd);
END;
@@
DELIMITER ;
You ARE DONE NJOY!!