Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aboe
Starting Member
4 Posts |
 Posted - 2011-02-07 : 07:22:47
|
| 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
5072 Posts |
Posted - 2011-02-07 : 17:05:11
|
| Define your triggers with the NOT FOR REPLICATION clause.Create Trigger |
 |
|
|
aboe
Starting Member
4 Posts |
Posted - 2011-02-08 : 02:57:56
|
[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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-08 : 13:19:45
|
| 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? |
|
|
|
aboe
Starting Member
4 Posts |
Posted - 2011-02-09 : 02:04:14
|
| 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. |
|
|
|
Ermin
Starting Member
1 Post |
Posted - 2014-07-22 : 05:36:10
|
| 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_xxxThe 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… |
|
|
|
|
|
|
|
|
One updated row fires trigger twice after merge re