Replication changing PK

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