Hi,

I have an issue with my replication at the moment. I will try to describe
the scenario accurately.

I am using MS SQL 2000 SP4 with Merge Replication. Users/Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on one
table which updates another, here is an example of the trigger:

"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @projTotal as money
declare @projId as int
declare @projcurrtype as int

select @projId = project_id from inserted
select @projcurrtype = proj_curr_type from qt_projects where project_id =
@projId

--Get project total from the sum of table [qt_quotes]
select @projTotal = (select
sum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_type,@projcurrtype)) as
quoteTotal from qt_quotes where project_id = @projId)

--Update projects record with new project total
update qt_projects
set proj_act_totl = @projTotal
where project_id = @projId"

I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers) What is
happening is a conflict is occuring with the following message:

"The row was inserted at Server.Publisher' but could not be inserted at
'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_qt_quotes_qt_projects'. The conflict occurred in database
'Publisher', table 'qt_projects', column 'project_id'."

What is also happening as a result of this conflict (I think) is the record
in question is getting deleted from the Publisher. This is causing huge
problems as it is proving quite difficult to get these records back in the
system due to identity values.

Can anyone guide me to what might be happeing here, is it the trigger?

Onre posible cause is that there is an indexed view which references the
table. If you do an update which affects the indexed view, SQL Server will
implement it as a deferred update ie a delete/insert pair. Please see
Simon's blog for more details:
http://sqljunkies.com/WebLog/simons/archive/2006/04/24/Indexed_view_update_performance.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .