Replication Triggers on replicated tables.

Howdy all. I set up Immediate Updating replication on AdventureWorks on 2005
Person.Address and AddressType tables. When this type of replication is
created, a replication trigger is created on the Publisher. However, this
replication trigger fires off the other (User) trigger on the table when a
row is updated, and they continue to fire each other off. I caught all this
in Profiler so Im sure this is whats happening. Anyways, the following
message is then displayed:
Maximum Stored Proc, function, trigger, or view nesting level exceeded
(limit 32).
Here are the triggers:
ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on [Person].[Address]
for update not for replication as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [Person].[Address] set msrepl_tran_version = newid() from
[Person].[Address], inserted
where [Person].[Address].[AddressID] = inserted.[AddressID]
ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Address]
SET [Person].[Address].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
END;
Someone must have encountered this before and have a workaround?
TIA, ChrisR
use set trigger order to have replication fire at the end or make your
triggers not for replication.
Can we see the table schema and triggers?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <ChrisR@.foo.com> wrote in message
news:uN6YxlRMHHA.3556@.TK2MSFTNGP03.phx.gbl...
> Howdy all. I set up Immediate Updating replication on AdventureWorks on
> 2005 Person.Address and AddressType tables. When this type of replication
> is created, a replication trigger is created on the Publisher. However,
> this replication trigger fires off the other (User) trigger on the table
> when a row is updated, and they continue to fire each other off. I caught
> all this in Profiler so Im sure this is whats happening. Anyways, the
> following message is then displayed:
>
> Maximum Stored Proc, function, trigger, or view nesting level exceeded
> (limit 32).
>
> Here are the triggers:
>
> ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on
> [Person].[Address] for update not for replication as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
>
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [Person].[Address] set msrepl_tran_version = newid() from
> [Person].[Address], inserted
> where [Person].[Address].[AddressID] = inserted.[AddressID]
>
>
> ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
> AFTER UPDATE NOT FOR REPLICATION AS
> BEGIN
> SET NOCOUNT ON;
>
> UPDATE [Person].[Address]
> SET [Person].[Address].[ModifiedDate] = GETDATE()
> FROM inserted
> WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
> END;
>
> Someone must have encountered this before and have a workaround?
>
>
> TIA, ChrisR
>
>
>
>

Replication Option

What you are referring to is a 'conflict'. Have a look in
the latest BOL for "conflict resolution, queued updating".
There are 3 types:
(1) Publisher wins and the subscription is reinitialized
(2) Publisher wins and overwrites subscriber value
(3) Subscriber wins and overwrites publisher value
In merge replication there are more, but this is because
transactional consistency is not maintained - it works at
the row level only.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks Paul~

Replication not updating the subscriber

I am having some trouble getting merge replication working. I have 1 server
acting as a publisher and distributer and another server acting as my
subscriber. Both are running SQL 2000 SP4.
I need both the publisher and subscriber to be able to update the database.
One database is for internal company apps and the other is for our web
server both of which need access to the same database.
The steps are as follows.
1. Create a new publication using the "Create Publication Wizare" and using
all the defaults. When I come to the "Specify Articles" dialog I add my
table articles using the defaults and a few minor changes as follows:
- Keep the existing table unchanged.
- Automatically assign and maintain a unique range for each
subscription.
- Range size at pub = 10000
- Range size at sub = 10000
- Threshold = 90
2. I run the snapshot agent
3. I PUSH a new subscription using the "Push Subscription Wizard", using all
the defaults.
So far I have not gotten this to work at all. I get one of two major errors.
1. Violation of Primary Key constraint 'tablename'. Cannot insert duplicate
key in object 'tablename'
2. (If I select "Drop the existing table and re-create it" when adding
articles) I get an error complaining that the table cannot be dropped
because of a foreign key relationship (which it does have).
3. If I try and add any data at the subscriber I get an error telling me I
have to run Sp_adjustpublisheridentityrange.
What is the correct process here.
1) can we get more info on the primary key violation. Where does this show
up? Do you see it in the conflict viewer?
2) Ideally your subscriber would be an empty database when applying the
subscriber. Can you drop the problem table on the subscriber before
distributing the snapshot.
3) Check the table on the subscriber and drop the replication check
constraint on the identity column.
"Robert Sheppard" <sheprts@.cox.net> wrote in message
news:%23xX47jx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I am having some trouble getting merge replication working. I have 1 server
> acting as a publisher and distributer and another server acting as my
> subscriber. Both are running SQL 2000 SP4.
> I need both the publisher and subscriber to be able to update the
> database.
> One database is for internal company apps and the other is for our web
> server both of which need access to the same database.
> The steps are as follows.
> 1. Create a new publication using the "Create Publication Wizare" and
> using
> all the defaults. When I come to the "Specify Articles" dialog I add my
> table articles using the defaults and a few minor changes as follows:
> - Keep the existing table unchanged.
> - Automatically assign and maintain a unique range for each
> subscription.
> - Range size at pub = 10000
> - Range size at sub = 10000
> - Threshold = 90
> 2. I run the snapshot agent
> 3. I PUSH a new subscription using the "Push Subscription Wizard", using
> all
> the defaults.
> So far I have not gotten this to work at all. I get one of two major
> errors.
> 1. Violation of Primary Key constraint 'tablename'. Cannot insert
> duplicate
> key in object 'tablename'
> 2. (If I select "Drop the existing table and re-create it" when adding
> articles) I get an error complaining that the table cannot be dropped
> because of a foreign key relationship (which it does have).
> 3. If I try and add any data at the subscriber I get an error telling me I
> have to run Sp_adjustpublisheridentityrange.
> What is the correct process here.
>
>