Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Triggers, Linked Server, and Transaction issue

Author  Topic  sjm Starting Member 1 Post Posted - 2007-06-04 : 14:11:03 I have an issue with an insert trigger copying data to a linked server. Server 1 hosts the application database. On one of the tables, I have an after insert trigger that copies the data to a database on a linked server (server 2). The problem is, if the trigger should fail for any reason I want the original insert on the table to succeed. I have been having difficulty in getting this to occur. Currently, if the trigger fails (if the linked server is unavailable, for example) the insert on the table on server 1 is also rolled back. Originally, I expected that the after insert trigger would not interfere with the application’s insert. This is not the case. XACT_ABORT was turn on to support the insert on the linked server. If XACT_ABORT was not turned on, I received the 7395 error copied below. I tried wrapping the insert of the trigger in an explicit transaction and that also did not have the desired effect. I would like feedback on how to have the trigger succeed or fail without affecting the insert on the table. This is complicated by the linked server being the destination of the trigger’s copy. Server 1 is SQL 2000 (8.0.2039), Windows Server 2003 Enterprise. Server 2 is SQL 2005 (9.0.3054), Windows Server 2003 Standard. Trigger (try1): CREATE TRIGGER [dbo].[Staging_Insert] ON [dbo].[tablename] AFTER INSERT AS SET XACT_ABORT ON INSERT INTO Staging ( columns ) select columns FROM inserted Trigger (try2): CREATE TRIGGER [dbo].[Staging_Insert] ON [dbo].[tablename] AFTER INSERT AS SET XACT_ABORT ON BEGIN TRANSACTION A; INSERT INTO Staging ( columns ) select columns FROM inserted COMMIT TRANSACTION A; XACT_ABORT Error: [OLE/DB provider returned message: Cannot start more transactions on this session.] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096]. Msg 7395, Level 16, State 2, Procedure Staging_Insert, Line 3 Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF.: