I want to make an update trigger execute a Java program.
I have a table
T1 with a column named
Flag changes, I want to run a trigger that results in an execution of a Java program.
Is this possible?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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; |
result contains the exit code of the external program
There are other useful functions in this library:
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_execcan do pretty much everything, exposing the function poses a very real security hazard.
But anyway, I agree with the proposal of Remus Rusanu
I know this post is fairly old and sorry for picking it up but I recently had the same requirement, so I created a MySQL UDF that uses JNI to call Java code from within a MySQL trigger. I'm posting this as a reference for similar future requests. I've checked in the code in bitbucket. You can download it from here:
MySQLUDFJavaLauncher and here is also a link to the Instructions.
Maybe this will help others.