Trigger on replicated table- HELP

Question

I have two servers, transactional replication. I want to setup a trigger on the subscriber which will fire when a string shows up in a column in a table in the replicated database.

I tried:

USE [XXX]
GO
/****** Object:  Trigger [dbo].[XXXIOT]    Script Date: 04/04/2012 08:28:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER 
    [dbo].[XXXIOT]
ON 
    [dbo].[logfile0]
FOR INSERT
AS
  BEGIN
    SET NOCOUNT ON
      INSERT INTO [XXX].[Test].[dbo].[XXXX]
      (
    eventcode,
    cardname,
eventnumber,
eventdatetime,
pointname
      )
    SELECT
    eventcode,
    cardname,
eventnumber,
eventdatetime,
pointname
    FROM INSERTED
    WHERE EVENTCODE = 'Access Authorized - Local Access' AND POINTNAME LIKE 'XXX' OR POINTNAME LIKE 'YYY'
END

All replication stops when I add this one.

I tried adding NOT FOR REPLICATION - and I get no data.

Any clues?

---

John Lutheran

Answer 1

Hi John,

This should work as you are updating it into some other table and not on the same same table. What error you see into replication monitor?

---

Kindly mark the reply as answer if they help

Answer 2

I will try a table on the same server. This is the error from the replication monitor:

Command attempted:

if @@trancount > 0 rollback tran

(Transaction sequence number: 0x00000771000FB566000300000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB02TSSNT" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)

Get help: http://help/7391

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB02TSSNT" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)

Get help: http://help/7391

---

John Lutheran

Answer 3

When I alter the trigger to use the same server, it all works, no problem.

Is it permissions?

---

John Lutheran

Answer 4

I saw in you code, you are trying to insert the data into a table on some other server via linked server. It is issue with your linked server named DB02TSSNT. It is configured with DTC ON. Edit the linked server option and it should work.

---

Kindly mark the reply as answer if they help

Answer 5

Have you tried to do the same operation outside of the trigger? Just try to see if you can insert into the table using a linked server?

From the error it looks like the linked server is not configured properly. If the linked server is created by replication then it might not work. for that issue check this link

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 6

I can copy the rows with the exact same command outside the trigger. I tried deleting the link server and reconnecting it via the trick in the link you posted, same result. Still the trigger stops the replication. I am confused.

---

John Lutheran