Link to home
Start Free TrialLog in
Avatar of cf13
cf13

asked on

Insert into MySQL Table from MSSQL Insert Trigger

Hi there!

I'm now a few days on solving the following problem:

I have an application, that runs on a MSSQL Database. Now an other Application, running on an MySQL-Database.
Now - they share some data, which makes it necessary to transfer partial data between two tables.

The approach is now, to create a trigger on the MSSQL-Table (t1) and to write the data from "Insert"-Table to the MySQL-Table (t2).
Remember: I do only need a partial set of data, where column names on t1 and t2 vary. (ffieldms1-data will be filled to fieldmy1)

MySQL-Database is set up as Linked Server via ODBC 5.1 Driver.

My konzeptual Trigger is the follow:


CREATE TRIGGER myInsertTrigger
    ON [MSSQLDB].[dbo].[t1]
    FOR INSERT,UPDATE
    AS
		DECLARE @ID int
		SELECT @ID = (SELECT [id] FROM Inserted)
		PRINT @ID
		IF ( @ID >= 5000 ) AND ( @ID < 5999 )
        BEGIN
		SET XACT_ABORT ON
		BEGIN TRANSACTION
			INSERT INTO 
				OPENQUERY([MYSQL-REMOTE],'
				SELECT fieldmy1, fieldmy2
				FROM t1')
			SELECT fieldms1, fieldms2
			FROM Inserted

			DELETE FROM [MSSQLDB].[dbo].[t1] WHERE [ID] = @ID
		COMMIT TRANSACTION
		END

Open in new window


When Trigger is executed (e.g. with a update of ID to above range) following error occurs

The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[MYSQL][ODBC 5.1 Driver] Optional Feature not supported

I need specific help - google brings up a mass of threads, i did not find the right one.

If someone knows, how to configure the mssql-script, the mssql server or the mysql-server

THANK YOU
Avatar of dqmq
dqmq
Flag of United States of America image

Sorry, I cannot help so much with your specific problem.  However, can't overlook some other issues with your trigger.

1. Generally, not a good idea to start a transaction inside a trigger.

2. Your trigger will not work properly on a multiple record insert or update.

3. I don't understand the delete part.  Don't think you can delete the same record(s) you are inserting/updating in T1.   Perhaps need an "instead of" trigger.

See if attached trigger moves you ahead.
CREATE TRIGGER myInsertTrigger
    ON [MSSQLDB].[dbo].[t1]
    FOR INSERT,UPDATE
    AS

        BEGIN
        SET XACT_ABORT ON

        INSERT INTO OPENQUERY([MYSQL-REMOTE],'SELECT fieldmy1, fieldmy2 FROM t1')
			SELECT fieldms1, fieldms2
			FROM Inserted
                            WHERE Inserted.ID >= 5000 
                              AND Inserted.ID < 5999

        --don't think you can do this
	DELETE FROM [MSSQLDB].[dbo].[t1] WHERE [ID] in 
         (Select ID FROM Inserted
                            WHERE Inserted.ID >= 5000 
                              AND Inserted.ID < 5999)
       END 

Open in new window

Avatar of cf13
cf13

ASKER

I need to move the data from MSSQL to MySQL.

The solution i want is also only a workaround.
I can not insert data into the MySQL table.
Getting the error above.
Avatar of cf13

ASKER

I tried to execute my INSERT INTO OPENQUERY seperately.

Sure - With FROM t1 and some WHERE bla.

I Inserted this exact code into a Trigger.

ERROR!

Someone knows how to execute this trigger jobs like they were executed normally?
Avatar of cf13

ASKER

Now, i tried the following:

I packed my INSERT (into Mysql) Script into a STORED PROCEDURE with parameter - and...IT WORKS (manually)!

BUT:
If i execute the script from within trigger, i get exactly the same error.
My trigger now looks like

[code]
CREATE TRIGGER bla
    ON t1
    FOR INSERT,UPDATE
    AS
            DECLARE @ID int
            SELECT @ID = (SELECT [id] FROM Inserted)
            PRINT @ID
            IF ( @ID >= 5000 ) AND ( @ID < 5999 )
            BEGIN
              exec proce_dure @ID
            END
[/code]

Someone?
First, and this is NOT germaine to your problem, but something else to think about.  Inserted is a table.  which means it can have more than one row. Should it have more than one row, SELECT @ID=(Select ID from inserted)  will only return the the ID from one of the rows.  In other words, your trigger will miss some of the updates.


Second, and perhaps more relevant.  The error you were getting was related to distributed transactions.  That means updates on two different servers are being coordinated so that neither will succeed without the other.  That's probably a good thing for you, but it does make the update more complex.  I'm even suspecting from your error message that your ODBC provider does not support distributed transactions (or at least the way you have it configured).   So you need to get distributed transactions working.

The reason it works outside of the trigger is because, in that case, there is no transaction on the SQL Server side, thusNOT distributed.   But the trigger runs in the context of an insert/update on the SQL server side and that implies a transaction that must be coordinated with the update on MSSQL.  Thus a distributed transaction. You can easily prove/disprove my theory.  Change your standalone test to include an update on SQL Server.  Something like this:

--This works, because it is not distributed:
exec proce_dure @ID

--This fails (I'm guessing), because it is distributed
begin transaction
exec proce_dure @ID
commit transaction

--If my theory is correct, this may work. (The data integrity implications are ominous, but I will let you wrestle with that).



CREATE TRIGGER myInsertTrigger
    ON [MSSQLDB].[dbo].[t1]
    FOR INSERT,UPDATE
    AS

        BEGIN
        SET XACT_ABORT ON
        COMMIT TRANSACTION
        INSERT INTO OPENQUERY([MYSQL-REMOTE],'SELECT fieldmy1, fieldmy2 FROM t1')
			SELECT fieldms1, fieldms2
			FROM Inserted
                            WHERE Inserted.ID >= 5000 
                              AND Inserted.ID < 5999

       END 

Open in new window

P.S.
I still don't understand why you have gravitated to a trigger, which complicates the scenario.  You have this:

Insert to MS SQL table
Fires insert trigger
   If certain Id range
      Insert to MYSQL table
      Delete from MS SQL table

Why not this:
If certain ID range
   Insert into MYSQL table
Else
   Insert into  MS SQL Table
Avatar of cf13

ASKER

Thanks for your help, dqmq!

You said, i have to enable distributed transactions - can you tell me where to look?

I need a trigger, because every moment Application1 writes data to MSSQL-db, it has to be converted and instantly inserted into the other db. An option is to execute a stored procedure every few seconds - i do not know how this performs.

Your code does not work either.
I tried a trigger, where only a fixed insert is given to MYSQL (no Insert-table, nothing regarding to the current INSERT/UPDATE).
It gives me the same error.
Also i tried INSTEAD OF and AFTER (which is implicit with FOR).
Same error.

It seems like a Trigger itself works inside some distributed context...
So where do i enable this distributed transactions via my ODBC driver?
SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cf13

ASKER

Yeah right.
Meanwhile i found this solution by myself. It is like you suggested.
Thank you also for the great explanation of distributed transactions!

my way as follows:
[code]
CREATE TRIGGER processUpdate
    ON [table]
    FOR UPDATE
    AS
            INSERT INTO [temporaryTableWithSameStructure]              
            FROM Inserted
            SET IMPLICIT_TRANSACTIONS ON
            GO
            exec processUpdate_ProcedureWithMySQLAction
[/code]

I still did not test, if it works without the SET IMPLICIT_TRANSACTIONS ON