| Groups | Blog | Home |
sql server replication : Triggers and Merge replication
Hi,
I have a database that is replicated between 3 SQL servers at 3 different sites over a WAN link using merge replication. I want to use a trigger on a table to assign a sequential job number to records when one is needed. The trigger recognises certain conditions and determines if an updated row needs a job number. If it does, it updates the row with the job number. The job number given needs to be continuous amongst all the offices so the best way I could come up with to do this was to only put the trigger on 1 of the servers (server A). That way the next job number could be stored in a table and updated each time the trigger assigns one. I was hoping that when updates came from the other 2 servers they would replicate to Server A, the trigger would fire and update the record, then the new job number would get replicated back to the other 2 servers. But that doesn't seem to be the case. Sometimes it works, but other times the records in Server A are the only ones that are updated. The changes never go back to the other servers so I end up with different data at each server. Has anyone got any ideas why this is happening? I'm completely stuck. I've searched all over the internet and can't find any help anywhere. Failing that, does anyone have any suggestions on better ways of doing this?
sounds like you want to implement some sort of indentity range management across servers.
Replication due to its asynchronous nature and the loosely consistent nature of merge replication is not ideal for this. You should be using distributed transactions for a solution like this. However it only works when all three servers are online. -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Me" wrote:
> Hi, > > I have a database that is replicated between 3 SQL servers > at 3 different sites over a WAN link using merge > replication. > > I want to use a trigger on a table to assign a sequential > job number to records when one is needed. The trigger > recognises certain conditions and determines if an updated > row needs a job number. If it does, it updates the row > with the job number. > > The job number given needs to be continuous amongst all > the offices so the best way I could come up with to do > this was to only put the trigger on 1 of the servers > (server A). That way the next job number could be stored > in a table and updated each time the trigger assigns one. > I was hoping that when updates came from the other 2 > servers they would replicate to Server A, the trigger > would fire and update the record, then the new job number > would get replicated back to the other 2 servers. But that > doesn't seem to be the case. > > Sometimes it works, but other times the records in Server > A are the only ones that are updated. The changes never go > back to the other servers so I end up with different data > at each server. > > Has anyone got any ideas why this is happening? I'm > completely stuck. I've searched all over the internet and > can't find any help anywhere. > > Failing that, does anyone have any suggestions on better > ways of doing this? > > Thank you for any help!
Thanks but that's no good because our WAN links aren't
very reliable. Anything else I could try? [quoted text, click to view] >-----Original Message----- loosely consistent nature of merge replication is not >sounds like you want to implement some sort of indentity range management across servers. > >Replication due to its asynchronous nature and the ideal for this. You should be using distributed transactions for a solution like this. [quoted text, click to view] >
>However it only works when all three servers are online. >-- >Looking for a SQL Server replication book? >http://www.nwsu.com/0974973602.html > > > > >"Me" wrote: > >> Hi, >> >> I have a database that is replicated between 3 SQL servers >> at 3 different sites over a WAN link using merge >> replication. >> >> I want to use a trigger on a table to assign a sequential >> job number to records when one is needed. The trigger >> recognises certain conditions and determines if an updated >> row needs a job number. If it does, it updates the row >> with the job number. >> >> The job number given needs to be continuous amongst all >> the offices so the best way I could come up with to do >> this was to only put the trigger on 1 of the servers >> (server A). That way the next job number could be stored >> in a table and updated each time the trigger assigns one. >> I was hoping that when updates came from the other 2 >> servers they would replicate to Server A, the trigger >> would fire and update the record, then the new job number >> would get replicated back to the other 2 servers. But that >> doesn't seem to be the case. >> >> Sometimes it works, but other times the records in Server >> A are the only ones that are updated. The changes never go >> back to the other servers so I end up with different data >> at each server. >> >> Has anyone got any ideas why this is happening? I'm >> completely stuck. I've searched all over the internet and >> can't find any help anywhere. >> >> Failing that, does anyone have any suggestions on better >> ways of doing this? >> >> Thank you for any help! >> >.
I've just tested this and as far as I understand this is
normal behaviour. Your trigger is running in the same transaction as the original update from the merge synchronization, so as far as replication is concerned (the record in MSmerge_contents) the complete transaction involved in the update was as a result of replication, meaning the changes are not downloaded again to the subscriber. What you could do is do a dummy update to force the records down. To implement this selectively, your trigger could insert a bogus value eg -999. You have a job which looks for the value -999 and updates these records to the correct value, in which case replication will then send them down on the next synchronization. HTH, Paul Ibison
Hi Paul,
As I said though it does seem to work fine a lot of the time. Any reason for that? I will try what you suggested below. It's not an ideal solution but it's worth a go. Thanks [quoted text, click to view] >-----Original Message-----
>I've just tested this and as far as I understand this is >normal behaviour. Your trigger is running in the same >transaction as the original update from the merge >synchronization, so as far as replication is concerned >(the record in MSmerge_contents) the complete transaction >involved in the update was as a result of replication, >meaning the changes are not downloaded again to the >subscriber. > >What you could do is do a dummy update to force the >records down. To implement this selectively, your trigger >could insert a bogus value eg -999. You have a job which >looks for the value -999 and updates these records to the >correct value, in which case replication will then send >them down on the next synchronization. > >HTH, >Paul Ibison > > >.
I'm not totally sure if this will help you, but you can set the order that your triggers fire in by using, sp_settriggerorder, ie
sp_settriggerorder @triggername = 'MyTrigger', @order = 'first', @stmttype = 'UPDATE' -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Paul Ibison" wrote:
> In my test scenario, the only time it works is when the update itself is > done on the publisher anyway, or if the replication process/other process > updates the publisher record after the trigger has already been fired, so > the change goes down anyway but as the result of a different change. > Regards, > Paul Ibison > >
In my test scenario, the only time it works is when the update itself is
done on the publisher anyway, or if the replication process/other process updates the publisher record after the trigger has already been fired, so the change goes down anyway but as the result of a different change. Regards, Paul Ibison
Merge replication does not allow ping-pong movement of data by design.
If you insert data into publisher, then your trigger fires and inserts generated job number. Then it can be replicated without any problem because the trafic in this case goes only from publisher to sybscribers. If you insert data into synchronizer, then it is replicated to publisher. There your trigger fires, generates new job number and inserts it into publisher. Of course merge replication trigger is fired, but it skips transfer of data back to subscriber, because it thinks that it the same data that was transfered (from subscriber to publisher). You should see following condition in the begining of every trigger of merge replication: if sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1 This condition does the the checking which I described above. Question is what could be done to workaround that... I would propose modifying of the merge replication insert trigger on your jobs table in publisher database. You could modify condition somehow that it would allow execution of trigger when you need that. For example something like that: if ( sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1 ) OR ( sessionproperty('replication_agent') = 1 AND UPDATE( job_nr_column_name ) ) Hope it helps. -- Regards, Kestutis Adomavicius Consultant UAB "Baltic Software Solutions" [quoted text, click to view] "Me" <anonymous@discussions.microsoft.com> wrote in message news:1c22901c45220$4427d9f0$a101280a@phx.gbl... > Hi, > > I have a database that is replicated between 3 SQL servers > at 3 different sites over a WAN link using merge > replication. > > I want to use a trigger on a table to assign a sequential > job number to records when one is needed. The trigger > recognises certain conditions and determines if an updated > row needs a job number. If it does, it updates the row > with the job number. > > The job number given needs to be continuous amongst all > the offices so the best way I could come up with to do > this was to only put the trigger on 1 of the servers > (server A). That way the next job number could be stored > in a table and updated each time the trigger assigns one. > I was hoping that when updates came from the other 2 > servers they would replicate to Server A, the trigger > would fire and update the record, then the new job number > would get replicated back to the other 2 servers. But that > doesn't seem to be the case. > > Sometimes it works, but other times the records in Server > A are the only ones that are updated. The changes never go > back to the other servers so I end up with different data > at each server. > > Has anyone got any ideas why this is happening? I'm > completely stuck. I've searched all over the internet and > can't find any help anywhere. > > Failing that, does anyone have any suggestions on better > ways of doing this? > > Thank you for any help!
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |