Skip to content

Latest commit

 

History

History
40 lines (33 loc) · 2.31 KB

control-behavior-of-triggers-and-constraints-in-synchronization.md

File metadata and controls

40 lines (33 loc) · 2.31 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords dev_langs monikerRange
Control behavior of triggers & constraints in synchronization
Learn how to prevent triggers from executing or constraints from being enforced during the synchronization of a SQL Server Replication Publication.
MashaMSFT
mathoma
09/25/2024
sql
replication
how-to
updatefrequency5
identities [SQL Server replication]
constraints [SQL Server], replication
triggers [SQL Server], replication
triggers [SQL Server replication]
constraints [SQL Server replication]
NOT FOR REPLICATION option
NFR option
TSQL
=azuresqldb-mi-current||>=sql-server-2016

Control Behavior of Triggers and Constraints in Synchronization

[!INCLUDE SQL Server SQL MI] During synchronization, replication agents execute INSERT (Transact-SQL), UPDATE (Transact-SQL), and DELETE (Transact-SQL) statements on replicated tables, which can cause data manipulation language (DML) triggers on these tables to be executed. There are cases when you may need to prevent these triggers from firing or constraints from being enforced during synchronization. This behavior depends on how the trigger or constraint is created.

To prevent triggers from executing during synchronization

  1. When creating a new trigger, specify the NOT FOR REPLICATION option of CREATE TRIGGER (Transact-SQL).

  2. For an existing trigger, specify the NOT FOR REPLICATION option of ALTER TRIGGER (Transact-SQL).

To prevent constraints from being enforced during synchronization

  1. When creating a new CHECK or FOREIGN KEY constraint, specify CHECK NOT FOR REPLICATION option in the constraint definition of CREATE TABLE (Transact-SQL).

Related content