SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 One updated row fires trigger twice after merge re
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aboe
Starting Member

4 Posts

Posted - 02/07/2011 :  07:22:47  Show Profile  Reply with Quote
Hi,

We have a remote database on the DMZ which is merge replicated with our main database (PULL).
In our main database we have a trigger to log changes to table1. Updates from "inside" causes no problem. But when performing updates on remote database, inside trigger is fired twice for each remote update with some columns in each update.

Ex.

UPDATE table1 SET Name='Eric', City='Stockholm', ChangedBy='UserName'

After merge replication, update trigger is fired twice. First time with only column Name updated. Second time with column City and ChangedBy updated. There are no triggers on the remote computer, except for the system merge replication trigger.

It seems like if the one update is divided into two updates, with some columns in each, after replicated. Always the same columns.

The main problem is that the trigger can only find out who (ChangedBy) performed the update for some columns.

Really need help on this. Thank you.

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/07/2011 :  17:05:11  Show Profile  Visit russell's Homepage  Reply with Quote
Define your triggers with the NOT FOR REPLICATION clause.

Create Trigger
Go to Top of Page

aboe
Starting Member

4 Posts

Posted - 02/08/2011 :  02:57:56  Show Profile  Reply with Quote
[quote]Originally posted by russell

Define your triggers with the NOT FOR REPLICATION clause.

If I use the NOT FOR REPLICATION the trigger is not fired at all. This is not the problem. I want the trigger to be fired, but only once for each updated row. The problem is that the replication causes the trigger to fire twice (which is not th expected behaviour). If I let the trigger look in the "deleted" and "inserted", I can see that the first trigger execute, only half of the columns are updated. At the second execute, the other columns are updated.
(It is SQL2005 and a merge replication (pull) with conflict handling on column level.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/08/2011 :  13:19:45  Show Profile  Visit russell's Homepage  Reply with Quote
If the row updates aren't all in the same transaction then nothing you can do about the trigger firing multiple times.

You could make the trigger not for replication, then execute the logic within the trigger after the synch.

What does the trigger do?
Go to Top of Page

aboe
Starting Member

4 Posts

Posted - 02/09/2011 :  02:04:14  Show Profile  Reply with Quote
The trigger is used for logging changes to the table. It compares columns in deleted and inserted to find out what has been changed. The changed columns (old and new values) are then inserted into a Log-table along with info about who has performed the change. This is sorted out from a field in the table, RowUpdatedBy, which is always filled in at all updates. The problem here is that the RowUpdatedBy column is only present in one of the to transactions even if there is only one update performed.

If we do not use a trigger, it might not be possible to find the values before the update.
Go to Top of Page

Ermin
Starting Member

1 Posts

Posted - 07/22/2014 :  05:36:10  Show Profile  Reply with Quote
Hi aboe,
I know it’s been a while, but… I have exactlly the same troubles with SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).
We are logging data changes with table-triggers in a separate database called DB_History. The history database is not replicated.
My case it’s even worse. My merge publication contains a table with 177 columns. After a data change in one column on the publication database, the trigger fires on the subscriber database 177 times – it fires for each column one update statement with exec MSmerge_upd_sp_xxx
The property “Multicolumn UPDATE” is set to “True” in “Merging Changes” in the publication article properties. The property “Tracking level” is set to “Row-level tracking”.
All these settings seem to be ignored!
How did you solve this unacceptable behavior?
Any hint to this problem would be very helpful…
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000