Invoking a PHP script from a MySQL trigger

The trigger is executed on the MySQL 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 here: https://github.com/mysqludf/lib_mysqludf_sys#readme

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

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 sometableFOR EACH ROWCALL 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.

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.

ADDED 19.03.2014:

I should have added some reasoning earlier, but only found time to do this now. Thanks to @cmc for an important remark. So, PHP triggers add the following complexities to your application:

• Adds a certain degree of security problems to the application (external PHP script calls, permission setup, probably SELinux setup etc) as @Johan says.

• Adds additional level of complexity to your application (to understand how database works you now need to know both SQL and PHP, not only SQL) and you will have to debug PHP also, not only SQL.

• Adds additional point of failure to your application (PHP misconfiguration for example), which needs to be diagnosied also ( I think trigger needs to hold some debug code which will log somwewhere all insuccessful PHP interpreter calls and their reasons).

• Adds additional point of performance analysis. Each PHP call is expensive, since you need to start interpreter, compile script to bytecode, execute it etc. So each query involving this trigger will execute slower. And sometimes it will be difficult to isolate query performance problems since EXPLAIN doesn't tell you anything about query being slower because of trigger routine performance. And I'm not sure how trigger time is dumped into slow query log.

• Adds some problems to application testing. SQL can be tested pretty easily. But to test SQL + PHP triggers, you will have to apply some skill.