Replication Triggers on replicated tables. -- sql server replication

ChrisR
1/5/2007 3:32:47 PM

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

lord.fist
1/6/2007 3:04:50 PM

[quoted text, click to view]

[quoted text, click to view]

lord.fist
1/6/2007 3:06:49 PM

[quoted text, click to view]

Um make that IF ( (SELECT trigger_nestlevel() ) > 2 ) RETURN

Hilary Cotter
1/7/2007 8:19:04 AM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Replication Triggers on replicated tables.