| Product: | Syniti Data Replication, DBMoto |
| Version: | All |
| ID: | 2027 |
| Summary: | Using 'SET NOCOUNT ON' in Triggers on a SQL Server target table to avoid reports of multiple updates/inserts |
-
Q: During a mirroring replication to SQL Server, the table is regularly reporting:
“An UPDATE transaction has updated 2 records. Verify the key setting for the target table."
I have verified that the table has the correct primary key, and that this is still reflected in Metadata and of course that there is only one relevant row to be updated. What is causing this message?
A: These warnings can be caused by triggers that you have implemented on the target table. If an INSERT or UPDATE from the Syniti DR/DBMoto replication causes a trigger to fire that inserts or updates additional records, you can get a result which says more than one record was inserted or updated. The best way to handle this situation is to call the "SET NOCOUNT ON" Transact-SQL statement at the beginning of the trigger.
For more information, see the Microsoft SQL Server Transact-SQL Reference Guide: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql