MS SQL Server/Linked Server & Trigger
Actually Sir, initially I was not using commit over there but at that time another error was
"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.