3

We have a website that stores form data in a web hosted MySQL database. Whenever form data is entered into the MySQL server tables I need update all necessary tables on our local SQL Server database.

I have seen several articles that describe how to update MySQL using SQL Server triggers but nothing that goes the other way.

If I create a linked server and linked tables in SQL Server can I create a trigger on a MySQL table from the SQL Server management studio?

3

You can't "see" the SQL Server from within MySQL like you can in the reverse direction with a linked server.

The only way I can think of doing this is to create a MySQL trigger that calls a User Defined Function that executes an external application and passes the new data to it. The external application would have to connect to the SQL Server database and push the data to it.

DELIMITER @@

CREATE TRIGGER Test_Trigger 
AFTER INSERT ON MyTable 
FOR EACH ROW
BEGIN

DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd=CONCAT('/usr/bin/php ', 'home/sync/send_to_sql_server.php f1=', NEW.f1, ' f2=', NEW.f2);
SET result = sys_exec(cmd);

END;
@@
DELIMITER ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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