Invoking a PHP script from a mysql trigger

Question

Is there anyway invoke a PHP page / function when a record being inserted in to the mysql db table. We dont have control over the record insertion procedure.Is there some thing called trigger which can call a PHP script back ?

Answer 1

The trigger is executed on the SQL server, not on the PHP one (even if those are both on the same machine).

So, I would say this is not quite possible -- at least not simply.

Still, considering this entry from the MySQL FAQ on Triggers :

23.5.11: Can triggers call an external application through a UDF?

Yes. For example, a trigger could invoke the sys_exec() UDF available at MySQL Forge here: http://forge.mysql.com/projects/project.php?id=211

So, there might be a waty, actually, via an UDF function that would launch the php executable/script ; not that easy, but seems possible ;-)

Answer 2

If you have transaction logs in you MySQL, you can create a trigger for purpose of a log instance creation. A cronjob could monitor this log and based on events created by your trigger it could invoke a php script. That is if you absolutely have no control over you insertion.

Answer 3

I found this:

http://forums.mysql.com/read.php?99,170973,257815#msg-257815

DELIMITER $$
CREATE TRIGGER tg1 AFTER INSERT ON `test`
FOR EACH ROW
BEGIN
\! echo "php /foo.php" >> /tmp/yourlog.txt
END $$
DELIMITER ;

Answer 4

That should be considered a very bad programming practice to call PHP code from a database trigger. If you will explain the task you are trying to solve using such "mad" tricks, we might provide a satisfying solution.

Answer 5

i want mysql and mssql to synchronize .. i want to make few changes to the columns while synchronizing .. can i make trigger from mysql to cal perl script which would do the needful .. if not plz suggest me the way

Answer 6

A friend and I have figured out how to call Bernardo Damele's sys_eval UDF, but the solution isn't as elegant as I'd like. Here's what we did:

1. Since we're using Windows, we had to compile the UDF library for Windows using Roland Bouman's instructions and install them on our MySQL server.
2. We created a stored procedure that calls sys_eval.
3. We created a trigger that calls the stored procedure.

Stored Procedure code:

DELIMITER $$
CREATE PROCEDURE udfwrapper_sp
(p1   DOUBLE,
 p2   DOUBLE,
 p3 BIGINT)
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result CHAR(255);
 SET cmd = CONCAT('C:/xampp/php/php.exe -f "C:/xampp/htdocs/phpFile.php" ', p1, ' ', p2, ' ', p3);
 SET result = sys_eval(cmd);
END$$;

Trigger code:

CREATE TRIGGER udfwrapper_trigger AFTER INSERT ON sometable
FOR EACH ROW
CALL udfwrapper_sp(NEW.Column1, NEW.Column2, NEW.Column3);

I'm not thrilled about having the stored procedure, and I don't know if it creates extra overhead, but it does work. Each time a row is added to sometable, the trigger fires.

Answer 7

@FractalizeR

why would you call this a very bad programming practice? do you have a better solution? for e.g look at this i need to generate a XML file from the data in mysql table on each update/insert and need to send it across to the client, one solution is to keep running a cron job an looking for modified data other is using triggers, is it possible do u have a solution?

Answer 8

I was thinking about this exact issue for a case with long polling where I didn't want the php script to have to continually poll the db. Polling would need to be done somewhere, memory would probably be best. So if somehow the trigger could put the info into something like memcache, then php could poll that would would be much less intensive overall. Just need a method for mysql to use memcache. Perhaps into a predefined variable with a specific user id. Once the data is retrieved php could reset the var until the db sets it again. Not sure about timing issues though. Perhaps a second variable to store the previous key selected.