473,728 Members | 1,720 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Merge Replication and Trigger Problem

Hi,

I have an issue with my replication at the moment. I will try to
describe the scenario accurately.

I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:

"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @projTotal as money
declare @projId as int
declare @projcurrtype as int

select @projId = project_id from inserted
select @projcurrtype = proj_curr_type from qt_projects where project_id
= @projId

--Get project total from the sum of table [qt_quotes]
select @projTotal = (select
sum(dbo.fConver tCurrency(quot_ grnd_totl,quot_ curr_type,@proj currtype))
as quoteTotal from qt_quotes where project_id = @projId)

--Update projects record with new project total
update qt_projects
set proj_act_totl = @projTotal
where project_id = @projId"

I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:

"The row was inserted at Server.Publishe r' but could not be inserted at
'Subscriber.dat abase'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_q t_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."

What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.

Can anyone guide me to what might be happeing here, is it the trigger?

Jan 4 '07 #1
8 7878
Benzine wrote:
Hi,

I have an issue with my replication at the moment. I will try to
describe the scenario accurately.

I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:

"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @projTotal as money
declare @projId as int
declare @projcurrtype as int

select @projId = project_id from inserted
select @projcurrtype = proj_curr_type from qt_projects where project_id
= @projId

--Get project total from the sum of table [qt_quotes]
select @projTotal = (select
sum(dbo.fConver tCurrency(quot_ grnd_totl,quot_ curr_type,@proj currtype))
as quoteTotal from qt_quotes where project_id = @projId)

--Update projects record with new project total
update qt_projects
set proj_act_totl = @projTotal
where project_id = @projId"
First thing to notice here is that your trigger is going to have issues
with any multi-row insert/update/delete statements. You need to get
that fixed.

However, I don't think that's your problem...
I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:

"The row was inserted at Server.Publishe r' but could not be inserted at
'Subscriber.dat abase'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_q t_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."

What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.

Can anyone guide me to what might be happeing here, is it the trigger?
Merge replication is funny. So far as I can work out, in 2000, you
cannot force the merges to happen in a particular order. So it's
possible for it to merge data in the referencing table before it merges
data in the referenced table, for a particular foreign key.

In our systems, we've marked all of the foreign keys as "not for
replication", which has eliminated these kinds of errors for us. I'm
not sure what your options are if you cannot cope with "orphan" rows
appearing for brief moments of time.

On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?

Damien

Jan 4 '07 #2
Thanks for your reply Damien,

Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"

Damien wrote:
Benzine wrote:
Hi,

I have an issue with my replication at the moment. I will try to
describe the scenario accurately.

I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:

"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @projTotal as money
declare @projId as int
declare @projcurrtype as int

select @projId = project_id from inserted
select @projcurrtype = proj_curr_type from qt_projects where project_id
= @projId

--Get project total from the sum of table [qt_quotes]
select @projTotal = (select
sum(dbo.fConver tCurrency(quot_ grnd_totl,quot_ curr_type,@proj currtype))
as quoteTotal from qt_quotes where project_id = @projId)

--Update projects record with new project total
update qt_projects
set proj_act_totl = @projTotal
where project_id = @projId"
First thing to notice here is that your trigger is going to have issues
with any multi-row insert/update/delete statements. You need to get
that fixed.

However, I don't think that's your problem...
I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:

"The row was inserted at Server.Publishe r' but could not be inserted at
'Subscriber.dat abase'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_q t_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."

What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.

Can anyone guide me to what might be happeing here, is it the trigger?

Merge replication is funny. So far as I can work out, in 2000, you
cannot force the merges to happen in a particular order. So it's
possible for it to merge data in the referencing table before it merges
data in the referenced table, for a particular foreign key.

In our systems, we've marked all of the foreign keys as "not for
replication", which has eliminated these kinds of errors for us. I'm
not sure what your options are if you cannot cope with "orphan" rows
appearing for brief moments of time.

On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?

Damien
Jan 4 '07 #3
Benzine wrote:
Thanks for your reply Damien,

Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"
Um, yes, I believe so. (Being a poncy type, I tend to do all this kind
of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).

Damien

Jan 4 '07 #4
Damien wrote:
On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?
I suppose it could work if (1) the order is reversed for deletes, either
automatically or by request, or (2) cascade-delete triggers are used.
Jan 4 '07 #5
Thanks allot for your help.
I will make the changes and issue a new snapshot then cross my fingers.

On Jan 5, 1:02 am, "Damien" <Damien_The_Unb elie...@hotmail .comwrote:
Benzine wrote:
Thanks for your reply Damien,
Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"Um, yes, I believe so. (Being a poncy type, I tend to do all this kind
of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).

Damien
Jan 4 '07 #6
Hi Damien,

You wouldnt by chance have a script that can update the
status_for_repl ication to Not_For_Replica tion for all FK's?

Ben

On Jan 4, 7:37 pm, "Damien" <Damien_The_Unb elie...@hotmail .comwrote:
Benzine wrote:
Hi,
I have an issue with my replication at the moment. I will try to
describe the scenario accurately.
I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:
"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @projTotal as money
declare @projId as int
declare @projcurrtype as int
select @projId = project_id from inserted
select @projcurrtype = proj_curr_type from qt_projects where project_id
= @projId
--Get project total from the sum of table [qt_quotes]
select @projTotal = (select
sum(dbo.fConver tCurrency(quot_ grnd_totl,quot_ curr_type,@proj currtype))
as quoteTotal from qt_quotes where project_id = @projId)
--Update projects record with new project total
update qt_projects
set proj_act_totl = @projTotal
where project_id = @projId"First thing to notice here is that your trigger is going to have issues
with any multi-row insert/update/delete statements. You need to get
that fixed.

However, I don't think that's your problem...
I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:
"The row was inserted at Server.Publishe r' but could not be inserted at
'Subscriber.dat abase'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_q t_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."
What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.
Can anyone guide me to what might be happeing here, is it the trigger?Merge replication is funny. So far as I can work out, in 2000, you
cannot force the merges to happen in a particular order. So it's
possible for it to merge data in the referencing table before it merges
data in the referenced table, for a particular foreign key.

In our systems, we've marked all of the foreign keys as "not for
replication", which has eliminated these kinds of errors for us. I'm
not sure what your options are if you cannot cope with "orphan" rows
appearing for brief moments of time.

On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?

Damien- Hide quoted text -- Show quoted text -
Jan 5 '07 #7
Benzine wrote:
Hi Damien,

You wouldnt by chance have a script that can update the
status_for_repl ication to Not_For_Replica tion for all FK's?

Ben
I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.

On the second project, I had it included from the start, so I've never
had to do this again.

Damien

Jan 5 '07 #8
Thanks again.

Damien wrote:
Benzine wrote:
Hi Damien,

You wouldnt by chance have a script that can update the
status_for_repl ication to Not_For_Replica tion for all FK's?

Ben
I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.

On the second project, I had it included from the start, so I've never
had to do this again.

Damien
Jan 5 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
4822
by: Karthik | last post by:
Hi, We have setup a publisher and a distributor in SQL 2000 running SP3. Alongwith this we have setup a merge replication agent and its running successfully inside the same network. I allow anonymous subscriptions and the merge publication works successfully for anonymous pull subscriptions also. Now i am trying to setup the same scenario to setup replication over the Internet using FTP. I have created a FTP site and given certain...
0
2046
by: Gert Wurzer | last post by:
Hi! I hope anyone can help me with this very important problem! Since configurig one subscriber in our merge replication scenario to a subscribing publisher we get a lot of merge conflicts type 10 reason code 2. Because we have the german version i translate the reason text as meaningful as possible: The row was updated at 'publisher.database' but could not be updated
0
1933
by: hd | last post by:
Are there any standard approaches used to purge records from database in merge replication senario ? We are using merge replication between two sql server 2000 databases. These databasess have differnt purging frequencies ( purging frequency of subscriber database is 6 month where as that of publisher is year). Is there anyway to purge data from subscriber and publisher without getting these deletion reflected in replication ( basically...
3
4087
by: jaykchan | last post by:
One of the table that is in a merge replication somehow is missing an index. Strangely, only the table in one of the subscriber of the merge replication is missing the index; another subscriber and the publisher of the merge replication don't have this problem. How should I add the missing index back to that table? My understanding is that making structural change on a table that is in merge-replication is different from making change on...
1
1759
by: tedd_n_alex | last post by:
I have database on SQL Server 2000 set up with a merge publication. This publication is configured with a number of dynamic filters to reduce the amount of data sent to each client. Each client has an anonymous pull subscription. The merge process can be triggered by the windows sync manager and my application. To improve performance I have created some helper tables to hold the mapping between user login and primary keys of selected...
1
2028
by: EoRaptor013 | last post by:
We have a situation almost exactly like that in the MS documentation vis a vis peer-to-peer replication. We have three servers and three user groups, one each in Chicago, New York, and Bermuda. Because of our business practices, we are fairly confident that only one group will be updating a given record at any one time but the users will be updating their own local database servers and those updates must appear on all three servers....
0
1401
by: sk.rasheedfarhan | last post by:
Hi all, I set the configuration for Merge replication for Subscription on one database and I have created the Merge replication for publication on another machine. And I updated columns of subscription database table and I find publication machine database table also updated with subscription database table, here my problem starts, My problem is when I run replication for merge transaction, I have to find instance of the Windows on my...
0
1284
by: Piotr Wojtysiak | last post by:
Hi. I have a problem with trigger FOR in merge-replication. My trigger is not "NOT FOR REPLICATION" and it's modified table with beyond replication. In replication this trigger not fire. Why? Thank's for help. PW
0
2208
by: ho.horace | last post by:
Dear All, I am using SQL 2005 as pub and SQL EXPRESS as sub with Merge replication. Got the following error message The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber. Error Detail: The schema script 'CD_InTransit_v_153.sch' could not be propagated to
0
8753
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9409
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9263
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9188
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9121
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6012
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4522
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4787
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3229
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.