You are here:

MS SQL Server/Linked Server & Trigger


Actually Sir, initially I was not using commit over there but at that time another error was
thowing :
"Msg 7390, Level 16, State 2, Procedure Product_insert, Line 18
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked
server "MYSQL_INBORN_TALENT" does not support the required transaction interface."

Infact data is neither inserts in sql server 2012 nor mysql.

But when I write commit in my trigger atleast data is being inserting in both the server with msg
that (1 row(s) affected)
and an error :
"(1 row(s) affected)
Msg 3609, Level 16, State 1, Procedure sp_tbl_1_insert, Line 11
The transaction ended in the trigger. The batch has been aborted."

I apologize for the delay. I was laid low by a nasty cold. Anyway, I've done some checking and researching (I've never done a linked server to MySQL), and the consensus among those I talked to and internet forums is that what you're trying is a bad idea. The gist of it was that at best writing to a linked MySQL table from inside a SQL trigger would be AT BEST "problematic"... as you're discovering.  

Also: I examined your code and had a follow-up question which may or may not be related to your issue(s). Are you taking steps to insure only a single row is insert into the base table per transaction? The trigger code seems to assume that, but I can see how it might cause issues if, in fact,  you have a transaction which inserts multiple rows.

MS SQL Server

All Answers

Answers by Expert:

Ask Experts


David Vaughn


Performance tuning; T-SQL syntax;


Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Truman State University

©2016 All rights reserved.