Trigger in the Subscription database - Transactional Replication...

Just want to rule out some obvious.

1) Are your three triggers replicated to the subscriber

2) Do you use three-part name to update table in your triggers When you insert a row at subscriber, the fired trigger might directly modify the tables at publisher directly.

>> Three-part name is used in the first trigger alone (DBName.dbo.Sample). In the second trigger the table is updated by giving the table name only.

I assume the DBName in the three-part name in the first trigger is publication database. When you insert a row at subscriber, the trigger fired at subscriber DB will try to modify the Sample table in the publication database in the same transaction (due to three-part name). Next time when queue reader runs, it will find subsciber did INSERT and publisher did UPDATE.

Please let me know if my understanding is not correct.

Peng

What exactly does the trigger do

Here is a quick thought, if your trigger one updates the sample table upon insertion and the subscriber has the same trigger, it means that when a record is inserted into "sample" on the publisher side, some columns in the "sample" get changed, now the new row and the changes that trigger made will get replicated to your subscriber. And if your subscriber has the same trigger, it would want to change "some" columns in sample at the subscriber side that the same trigger has already updated on the publisher "sample" table and being replicated to subscribing table. And I can see how that can be a problem... but it's just my speculation.

Gary

Hi Gary,

The triggers are set NOT For Replication and so, it will not fire for replicated records. And also, the inserts are happening only on the subscriber end. These are the observations when a record is inserted in the subscriber db.

When we insert a record in the Sample table of the subscription database, record inserted successfully in the table with correct value population from both the triggers. But during replication synchronization, there is a conflict raised and record has been removed from the Sample table of the subscription database itself. The record is not replicated to the publisher and the other Subscribers also.

But when we comment update command in either in first or in second trigger, the insert works fine and record replicated to publisher and to other Subscribers.

We are not able to find the exact reason, why the record getting removed when we have updates in both the Triggers.

It would be helpful if u could provide us the reply soon.

Thanks and Regards,

Swapna.B.

Are the triggers created on subscriber table set to "Not For Replication" as well

Perhaps, a simple repro on your schema and trigger would help to troublshoot this.

Gary

Hi Gary,

The triggers created on the subscriber table is also set to NOT FOR REPLICATION.

I have given below the scripts to create table and triggers to reproduce the scenarios. As given in the previous threads, we are using Transactional Replication with Updatable Subscriptions.

We are using SQL Server 2005 SP1 with below Subscription details.

Subscription Agent Type : Pull Subscription

Subscription update mode : Queued failover

Subscription schedule : Continuous Running Mode

Conflict Resolution Policy : Publisher Wins

Table Creation Script :

USE [DBNAME]
GO
/****** Object: Table [dbo].[Sample] Script Date: 02/12/2007 15:21:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sample](
[ID] [int] NOT NULL,
[Date] [datetime] NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CasID] [int] NULL CONSTRAINT [DF_Sample_CasID] DEFAULT ((255)),
[msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT [MSrepl_tran_version_default_492A69F2_F129_468E_AD79_73E3BEB46826_2058490412] DEFAULT (newid()),
CONSTRAINT [PK_Sample] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Trigger Creation Script - Trigger 1 :

USE [DBNAME]
GO
/****** Object: Trigger [dbo].[TRG_Sample] Script Date: 02/12/2007 15:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRG_Sample] on [dbo].[Sample]
FOR INSERT, UPDATE
--DBReplication: Added NOT FOR REPLICATION to avoid firing while doing Replication
NOT FOR REPLICATION
AS

DECLARE
@ID int,
@phyCasId int
begin

set @phyCasID = -1;
select @ID = ID, @phyCasId = CASID
from inserted

If (@phyCasId Is Null OR @phyCasId = 255)
begin
Update Sample
Set CASID = 100
Where ID = @ID
end
end

Trigger 2 :

USE [DBNAME]
GO
/****** Object: Trigger [dbo].[Trg_Sample_DateIU] Script Date: 02/12/2007 15:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create TRIGGER [dbo].[Trg_Sample_DateIU] ON [dbo].[Sample]
FOR INSERT, UPDATE

NOT FOR REPLICATION
AS

SET NOCOUNT ON

UPDATE Pos.Dbo.Sample
SET Date = GetDate()
WHERE ID In (SELECT ID FROM Inserted)
AND Date Is Null

SET NOCOUNT OFF

Insert Script for Publisher:

INSERT INTO [DBNAME].[dbo].[Sample] ([ID], [Date], [Name], [CasID]) VALUES (4, null, 'Publisher', null)

Insert Script for Subscriber:

INSERT INTO [DBNAME].[dbo].[Sample] ([ID], [Date], [Name], [CasID]) VALUES (104, null, 'Subscriber', NULL)

Kindly let us know about this issue asap.

Thanks in advance.

Regards,

Swapna.B.

Swapna,

I did a quick test with the repro script you posted it (on a one machine setting), can't seem to get problem you were mentioning. However, I did notice that in your second create trigger script, you have a hardcoded reference to Pos.Dbo.Sample. You might want to make sure that the hard coded reference is pointing to the correct table. If this trigger is created exactly the same on publisher and subscriber, then you will have problem, because both triggers will update the same data (either on publisher side or subsriber side).

Can you check to see if might be the problem

Gary