May 2, 2003 at 5:09 pm
Hi,
We are attempting replication (for the first time) to provide a warm standy database but have a problem with our own triggers firing. All our tables have an Update trigger that tracks number of times changed and date of last change of each row plus certain parent tables create child records on Insert.
We have done the following:
1) We are using Trans Rep with queuing and push replication.
2) The NOT FOR REPLICATION line was added to our own triggers just after the AS statement.
3) The snapshot was created using drop and reapply tables and copy across all proc, triggers etc.
4) The snapshot works fine. The subscriber database is an exact copy and all triggers are there including the "NOT
FOR.." statement.
5) When we change a row (using EM) we get an error along the lines of "nested trigger error - limit of 32 reached".
6) From this we assumed our own triggers are being fired.
7) If we add the line:
IF UPDATE (msrepl_tran_version) RETURN
after the AS in our own triggers row changes are replicated correctly with no errors.
While this working ok for initial testing we don't want to put this work-around into production as it clearly isn't correct.
Can someone confirm that the "Not for replication" statement should stop our own triggers from firing.
This seems like a basic problem caused by sometype of configuration mistake we have made. Any ideas would be appreciated.
TIA - Peter
May 2, 2003 at 6:50 pm
The NOT FOR REPLICATION option goes before the AS keyword. If you really have it after the AS, you should have gotten an error when creating the trigger. Verify this first before we get any further into the problem.
Jay Madren
Jay Madren
May 2, 2003 at 11:11 pm
Hi Jay,
Correct - it was before the AS. We did indeed start off with it after the AS which did give a syntax error.
Cheers, Peter
May 4, 2003 at 9:33 pm
So, are you sure that the version of the trigger that got applied at the subscriber is the correct one? Just trying to think of explanations. I use the NOT FOR REPLICATION on dozens of triggers and all works as expected.
Also, when and where do you get the nested error? At the time of the original update at the publisher, or when the replication agent is proprogating the update at the subscriber?
Jay Madren
Jay Madren
May 4, 2003 at 10:57 pm
Hi Jay,
Thanks for that. Yes the trigger is applied correctly at the subscriber but the nesting error is on the publisher.
Now that you have provided confirmation that what we are trying to do is correct we will now go back to basics:
1) Blow away the database.
2) Recreate it from the source production database.
3) Republish it in our test system but with only only simply table as the only article in the publication.
4) See if the nesting error remains and, if it does, use xp_logevent to work out what triggers are being fired and in what order.
I will post the result asap.
Cheers, Peter
May 5, 2003 at 6:51 am
Hi Jay,
Ok, we have restored the database and published one article (tblACNAcntCodes). The snapshot and creating the subscription worked fine.
See below for the two triggers involved. AAAA is our own trigger and BBBB was added when we published the table.
The "EXEC master.." was added to both triggers so we could log what happens.
We use EM to change an existing record and we get the error:
"Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database.
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
Nothing is in the event log so neither of the "Master exec.." statements fired.
New records can be added in EM without error and they are correctly replicated to the subcriber. Any suggestions would be appreciated.
Cheers, Peter
AAAA----- This is our own trigger------------------------
CREATE TRIGGER ACNUpdate ON dbo.tblACNAcntCodes
FOR UPDATE
NOT FOR REPLICATION
AS
DECLARE @ID nvarchar(50)
SELECT @ID = ACNID from INSERTED
UPDATE tblACNAcntCodes SET
ACNUpdated = GETDATE(),
ACNUpdateCount = ACNUpdateCount + 1
WHERE ACNID=@ID
EXEC master..xp_logevent 60000,'ACN Update Trigger', informational
BBBB-----This is the trigger added at the Publisher when the---
----- table was replicated (exec master.. is our own addition--
------to this trigger -----------------------------------------
CREATE trigger [sp_MSsync_upd_trig_tblACNAcntCodes_1] on [dbo].[tblACNAcntCodes] for update as
declare @rc int
select @rc = @@ROWCOUNT
if @rc = 0 return
if update (msrepl_tran_version) return
update [dbo].[tblACNAcntCodes] set msrepl_tran_version = newid() from [dbo].[tblACNAcntCodes], inserted
where [dbo].[tblACNAcntCodes].[ACNID] = inserted.[ACNID]
EXEC master..xp_logevent 60000,'ACN Replication Trigger', informational
May 5, 2003 at 6:58 am
Aha - there's your problem. The NOT FOR REPLICATION option only prevents the trigger from firing when a replication agent is performing the action (insert, update or delete). The trigger still fires during the original action by the user. The idea behind NOT FOR REPLICATION is that you don't want the trigger to repeat the same action at the subscriber because the result of the trigger at the publisher is being replicated on its own, or otherwise just doesn't need to happen at the subscriber.
Anyway, you have a cyclic loop between your trigger and the replication trigger, since your trigger is updating something in the table, which causes the replication trigger to fire again to update the msrepl_tran_version column, which causes your trigger to fire again, round and round. So, you will need the "IF UPDATE (msrepl_tran_version) RETURN" line in your trigger to prevent this.
Jay Madren
Jay Madren
May 5, 2003 at 7:10 am
Hi Jay,
Thanks for the prompt response. So, as I undestand it, we need both "NOT FOR.." and "IF UPDATE (msrepl_tran_version) RETURN" in all our triggers. I was concerned that the "If Update.." was not needed if we were doing the right thing.
We will do this tomorrow (bed time in Oz now). I will let you know how we get on.
Cheers, Peter
May 7, 2003 at 2:26 am
Hi Jay,
machine problems hit and delayed work on replication. But we are looking good now although we hit problems with having the "If Update.." code in our Insert triggers. But we thought that through and are happy they should not be there for an insert and removed that line. Do you agree?
Tomorrow we will put the system through its paces with a load test to make sure the two db's are in sync and to check out latency etc.
Cheers, Peter
May 8, 2003 at 7:38 pm
Hi Jay,
Thanks for your help - all is working well. In our load test (2 x 1.6Ghz Pentiums with 1Gb memory) there was no noticable change to user response times. CPU usage was up a little and the transaction rate was about the same (around 30 per second) with peaks to 100 every few seconds - I guess this is when it pushed data to the distributor (also running on the data server).
Latency was excellent - no more than 5 seconds.
All in all very impressive.
Cheers, Peter
May 8, 2003 at 10:18 pm
I'm glad it's working well for you.
As to your earlier post, I agree that you don't need the IF Update code in your insert triggers. Sorry for not getting back to you. I've been "out of the loop" for a couple of days.
Jay Madren
Jay Madren
July 11, 2007 at 10:55 am
Hi Jay, I say your post about what can cause a cyclic loop between a table trigger and a replication trigger. We seem to be having this problem because we have tracking tables that coincide with our user tables and there are triggers on both the publisher and the subscriber. Can you tell me if we should add the Not for Replication clause to prevent this? Any help is much appreciated.
Thanks,
Chad
Chad E. Downey, CDMP - Certified Data Management Professional
Consultant - SQL Server, SSIS, SSRS, SSAS
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply