Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Replication / January 2006

Tip: Looking for answers? Try searching our database.

Replication changing PK

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
David Chase - 28 Jan 2006 18:38 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.