Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
6  
What if the transaction rollback later, will the execution be undone? Call back the missile? You do not solve this through triggers, you use queues and fire the program after commit. –  Remus Rusanu Apr 8 '13 at 19:08
    
Can you please explain a bit , what im doing is i have only two values in Flag i.e True and False , every time value toggles , java program should run –  user21546 Apr 8 '13 at 19:11
    
What database are we talking about? Is the Java program running inside the database (i.e. a Java stored procedure in Oracle)? Or are you trying to execute an external application? Does that application reside on the database server or on a different machine? –  Justin Cave Apr 8 '13 at 19:13
    
Database - MySql Java program is external programm resides on a same machine –  user21546 Apr 8 '13 at 19:14

2 Answers 2

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

share|improve this answer
    
how can i install the lib_mysqludf_sys utility ?? –  user21546 Apr 9 '13 at 12:53
    
Take a look into "installation" section here: github.com/mysqludf/lib_mysqludf_sys/blob/master/… ... but in general read the mysql udf manual –  Cristian Porta Apr 9 '13 at 19:22
    
getting this error -----CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so'; ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32) –  user21546 Apr 10 '13 at 13:01
    
do you have a 32bit or 64bit operating system? –  Cristian Porta Apr 10 '13 at 13:21
    
its 64 bit ubuntu 0.12... –  user21546 Apr 10 '13 at 13:25

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.