"David Chase" <dlchase@lifetimeinc.com> wrote in message
news:OA4dtoDJGHA.3120@TK2MSFTNGP10.phx.gbl...
>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" <dlchase@lifetimeinc.com> wrote in message
news:epRwmORJGHA.3856@TK2MSFTNGP12.phx.gbl...
> 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
>
> "David Chase" <dlchase@lifetimeinc.com> wrote in message
> news:OA4dtoDJGHA.3120@TK2MSFTNGP10.phx.gbl...
>>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
>>
>>
>
>