Linked MySQL Database, triggers do not update

Question

Help please,

I have a linked MySQL database in MS SQL 2000.

I can insert directly with: INSERT INTO OPENQUERY(Linked_DB, 'SELECT * FROM Product') values (1, 'Foo')

I have set up a trigger on the local DB table:

CREATE TRIGGER Product_insert ON [dbo].[Product]
FOR INSERT
AS
SET XACT_ABORT ON
INSERT INTO OPENQUERY(Linked_DB, 'SELECT * FROM Product')
SELECT ItemID, Description FROM INSERTED

However this causes an error message:

Server: Msg 7390, Level 16, State 1, Procedure Product_insert, Line 5

The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.

OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].

Thank you in advance

Answer 1

Here's some more information in the form of a workaround. The following trigger (sort of) works:

Create TRIGGER Product_insert ON [dbo].[Product]
FOR INSERT
AS

Declare @ID as Int
Declare @Desc as NVARCHAR(50)
SET @ID = (SELECT ItemID FROM INSERTED)
SET @Desc = (SELECT [Description] FROM INSERTED)

SET XACT_ABORT ON
Commit

INSERT INTO OPENQUERY(Linked_DB, 'SELECT * FROM Product')
VALUES (@ID, @Desc)

As you can see it stores the values of updated fields in variables, commits the transaction then inserts the values from the variables into the linked table.

There's two problems though:

1. It still doesn't work if you attempt to modify data direct in the DB table. (Same error message) It is only possible to insert records from a query.

2. You can only insert one record at a time. A query which would insert muliple records (eg INSERT INTO Product SELECT * FROM OldProducts) Causes an error: 

Server: Msg 512, Level 16, State 1, Procedure Product_insert, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Does this shed any light?