MSSQL query that puts results into MySQL database

I think open query is the key. I have set up a linked server which works. I think maybe its best to create a trigger that once specific data is entered into a field it copies this to my myql table. I need help..lol.

I want to make a trigger in a table that when a status column is populated with 1 of these 4 numebrs (2000,2010,2020,2030). It then runs a query that does the following. It looks up the ID of the current row Table A and uses this to find the related row in Table B.  

From Table B it then gets a lead ref from that row. It would now connect to a MySQL database via openquery (Linked database already set up)look up the ID in a Table C and in the status column enter a value based on the initial trigger.

So if 2000 was in the status column in Table A MSSQL. Now the mySQL Table C status would show 'Completed'.

If 2010 was in the status column in Table A MSSQL. Now the mySQL Table C status would show 'ONGOING'.
a
and so on...

I know this possible but not sure where to start.

via trigger: not recommended (if working at all), because mysql does not really support distributed transactions.

so, you can put a trigger, but just to insert into a "process" table the key values of the rows that updated, and than need to be "replicated" to the mysql table.

then, in a scheduled job, you can then perform the update (see this article):
https://www.experts-exchange.com/A_1517.html

I added a linked server and running the query works ok. But when I put it insidea  trigger it fails.


OLE DB provider "MSDASQL" for linked server "TEST" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".
Msg 7391, Level 16, State 2, Procedure STATUS_UPDATE, Line 6
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "TEST" was unable to begin a distributed transaction.

create trigger yourtriggername
on tableA
for insert,update
as
begin
update t
set t.status= case i.status  when 2000 then 'Completed' when 2011 then 'Ongoing' end
from inserted i
inner join tableB b
on b.id = i.id
inner join [linkedservername]...tablename t
on t.id = b.leadref
end

Select allOpen in new window

that is exactly what I wrote: you won't be able to do this in the trigger itself directly, but only in a dedicated job outside of the trigger.