I have a very strange thing happening where the primary key field is getting
changed when synchronizing with a subscriber. Background:
-Using SQL 2k and merge replication
-Testing with Publisher and Distributor on same server.
-Setup only 1 subscriber to test.
-Added a record into the Publisher into 2 tables (People and ApplicantInfo)
with a PK-FK link (1 to 1) on a field named PersonID. I also entered 9
records into a table with 1-many relationship to the ApplicantInfo table
(but not really relevant here).
-I check the records in the tables before I synchronized and both had a key
value of 195134582.
-I then synchronized from the subscriber and got the following conflict
error:
"The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted at
'DELLGX260.MCFIData'. The record can't be added or changed. Referential
integrity rules require a related record in table 'People'."
-I then opened EM and searched the People table on the Publisher and the
PersonID now has a value of 211587197.
-There is no update trigger on the People table, only delete trigger and
insert trigger. Below is the insert trigger on the People table. Could it
somehow affect the PersonID when inserting into subscriber? I hope not.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @randc WHERE PersonID = @newc
Can anyone shed light on this as it is critical to implementing replication.
Thank you.
David
David Chase - 29 Jan 2006 20:35 GMT
Turns out, the problem had to be the trigger as it fired again when synching
and changed the PersonID again. I removed the trigger and defaulted the
PersonID to random number and now it works fine.
David
>I have a very strange thing happening where the primary key field is
>getting changed when synchronizing with a subscriber. Background:
[quoted text clipped - 40 lines]
>
> David
Michael Hotek - 31 Jan 2006 15:33 GMT
This is actually a trigger ordering issue. Merge triggers are system
triggers and fire first. Than means your insert hit and fired the merge
trigger. Your trigger then fired causing an update to the same row which
was inserted. This will almost always cause a conflict to be thrown.

Signature
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
> Turns out, the problem had to be the trigger as it fired again when
> synching and changed the PersonID again. I removed the trigger and
[quoted text clipped - 46 lines]
>>
>> David