NULLS in Primary Keys
-
My Publisher has tables with triggers on them.
Let us suppose that I have a Tbl_Page and Tbl_MetadataItem table
If you add a record to either table then the trigger ensures that all combinations of page and metadata exist in a 3rd table called Tbl_PageMetadata.
This works perfectly.
My subscriber also had the triggers even though all tables were replicated. My subscriber also had primary keys on the same columns as the publisher.
Replication breaks because it tells me that it cannot insert a NULL value into a primary key field using sp_msins_Tbl_Page
This is ridiculous because the it should be copying the primary key from the publisher (and therefore NULLs cannot exist) to the primary key of the subscriber.
I know it is the trigger that is causing the problem but the trigger is simply SELECT fields from inserted. How can the virtual table inserted contain NULL values when its source was most definitely totally and absolutely not null?
-
David,
I am assuming that you are using Transactional replication. Do you need to update data at the subscriber? and if so what type of updaing are you using?
Are the Triggers marked as NFR?
* Noel -
Yes, continuous push replication. Only the tables are replicated.
I solved the problem by removing the triggers but the idea that the inserted table contains NULL values in the field used as a primary key on both publisher and subscriber really does my head in!
-
I believe that marking them as NFR will do! I came across something similar when trying to perform bidirectional Trans. Repl. and the One of the triggers was misssing the NFR
Glad you solved your problem
* Noel -
hi
is it possible to have null values in a primary key field
in sql server /oracle
-
hi
how come null values are alloved in a foreign key column which refers to a primary key column whih in fact doesnt allow null values?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply