Triggers and Merge replication

Me

unread,

Jun 14, 2004, 11:00:00 AM6/14/04

to

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!

Hilary Cotter

unread,

Jun 14, 2004, 11:20:01 AM6/14/04

to

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

Me

unread,

Jun 14, 2004, 11:33:25 AM6/14/04

to

Thanks but that's no good because our WAN links aren't
very reliable.

Anything else I could try?

>.
>

Paul Ibison

unread,

Jun 14, 2004, 11:36:33 AM6/14/04

to

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

Me

unread,

Jun 14, 2004, 11:54:51 AM6/14/04

to

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

>.
>

Paul Ibison

unread,

Jun 14, 2004, 12:05:41 PM6/14/04

to

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

Hilary Cotter

unread,

Jun 14, 2004, 12:52:03 PM6/14/04

to

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

Kestutis Adomavicius

unread,

Jun 15, 2004, 5:00:46 AM6/15/04

to

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"

"Me" <anon...@discussions.microsoft.com> wrote in message
news:1c22901c45220$4427d9f0$a101...@phx.gbl...