You are here:

MS SQL Server/Linked Server & Trigger

Advertisement


Question
Dear Sir,

I am using SQL Server 2012, I have solved my previous error by changing in my trigger :
USE [test_sql_to_mysql]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Product_insert] ON [dbo].[tbl_2]
FOR INSERT
AS
BEGIN
Declare @ID as Int
Declare @Desc as NVARCHAR(50)
SET @ID = (SELECT col_1 FROM INSERTED)
SET @Desc = (SELECT col_2 FROM INSERTED)

COMMIT TRANSACTION

INSERT INTO OPENQUERY(MYSQL_INBORN_TALENT, 'SELECT * FROM tbl_4')
VALUES (@ID, @Desc)

END
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?

Answer
Dear Ranjana,

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.

Regards,

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jwalant Natvarlal Soneji

Expertise

From general queries about SQL Severs both 2000 and 2005 to advanced features like Integration Services and Reporting Services.

Experience

Experience in the area: I have been working with database triggers, stored procedures, views, function, joins, dts, Query optimization. Education/Credentials: BE IT - 2005 Batch with First Class.

Publications
http://jwalantsoneji.com

Education/Credentials
BE IT, India

Awards and Honors
MCP

©2016 About.com. All rights reserved.