I am having problems using the MERGE statement in conjunction with replication (transactional with updatable subscriber - the problem only affect the subscriber).
As specified in the documentation:
For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same
action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.
Which can make sense but doesn't explain why when inserting a record using MERGE on a subscriber replicated table two replication triggers are fired one of which tries to insert NULL columns to the table failing replication.
I couldn't really find any documentation about that and the only thing left to do was removing all MERGE statements.
Does anyone have an idea on why it happens and has any suggestions?
Here our SQL vesion: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)
Thanks a lot.
View Complete Post