Posted by Benzine on 01/04/07 05:48

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

[Back to original message]