MS SQL Server/Linked Server & Trigger
I am using SQL Server 2012, I have solved my previous error by changing in my trigger :
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER TRIGGER [dbo].[Product_insert] ON [dbo].[tbl_2]
Declare @ID as Int
Declare @Desc as NVARCHAR(50)
SET @ID = (SELECT col_1 FROM INSERTED)
SET @Desc = (SELECT col_2 FROM INSERTED)
INSERT INTO OPENQUERY(MYSQL_INBORN_TALENT, 'SELECT * FROM tbl_4')
VALUES (@ID, @Desc)
Now data is inserting in both the sql server and mysql server both through trigger but along with insertion an error is also throwing :
(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.
Now how to rectify?
The issue is with TRANSACTION usage.
I would suggest you to read http://www.sqlteam.com/article/introduction-to-transactions
Basically, TRANSACTION should be started before you start any data changes and should be committed on completion of it successfully. This way add BEGIN TRANSACTION where the commit is right now and take down commit to justafter insert statement.
Feel free to follow-up.