Posted by Damien on 01/04/07 08:37

Benzine wrote:
> 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"
>
First thing to notice here is that your trigger is going to have issues
with any multi-row insert/update/delete statements. You need to get
that fixed.

However, I don't think that's your problem...

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

Merge replication is funny. So far as I can work out, in 2000, you
cannot force the merges to happen in a particular order. So it's
possible for it to merge data in the referencing table before it merges
data in the referenced table, for a particular foreign key.

In our systems, we've marked all of the foreign keys as "not for
replication", which has eliminated these kinds of errors for us. I'm
not sure what your options are if you cannot cope with "orphan" rows
appearing for brief moments of time.

On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?

Damien

[Back to original message]