Execution of a Java program by a trigger.

I want to make an update trigger execute a Java program.

I have a table T1 with a column named Flag. Whenever Flag changes, I want to run a trigger that results in an execution of a Java program.

Is this possible?

Yes, using the MySQL User-Defined Function (see MySQL 5.5 FAQ: Triggers) and installing the lib_mysqludf_sys

Then, for example, you can write your own trigger calling the sys_exec like this:

delimiter |
 CREATE TRIGGER testtrigger BEFORE UPDATE ON T1
  FOR EACH ROW BEGIN
   DECLARE result int(10);
   IF NEW.Flag <> OLD.Flag THEN  
     SET result = sys_exec('/path/to/javabin -jar your.jar');
     -- other kind of works and checks...
   END IF;
 END;
|

The result contains the exit code of the external program

There are other useful functions in this library:

• sys_eval : executes an arbitrary command, and returns it's output.

• sys_get : gets the value of an environment variable

• sys_set : create an environment variable, or update the value of an existing environment variable

• sys_exec : executes an arbitrary command, and returns it's exit code

More info here

Try it on a dev env and...


Be very careful in deciding whether you need this function. UDFs are
available to all database users - you cannot grant EXECUTE privileges
for them. As the commandstring passed to sys_exec can do
pretty much everything, exposing the function poses a very real
security hazard.

But anyway, I agree with the proposal of Remus Rusanu

StackExchange Database Administrators Q#39522, answer score: 7