مشاركة عبر

  • مقالة

Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION

In some cases, it is desirable for user activity in a replication topology to be treated differently from agent activity. For example, if a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table, it might not be required to enforce the same constraint when the row is inserted by a replication agent at the Subscriber. The NOT FOR REPLICATION option allows you to specify that the following database objects are treated differently when a replication agent performs an operation:

  • Foreign key constraints

    The foreign key constraint is not enforced when a replication agent performs an insert, update, or delete operation.

  • Check constraints

    The check constraint is not enforced when a replication agent performs an insert, update, or delete operation.

  • Identity columns

    The identity column value is not incremented when a replication agent performs an insert operation.

  • Triggers

    The trigger is not executed when a replication agent performs an insert, update, or delete operation.

When a table is published, schema options control how objects are created in the subscription database. The default schema options differ by publication. When options are set to specify that foreign key constraints and check constraints are created in the subscription database, the NOT FOR REPLICATION option is set. The NOT FOR REPLICATION option is also set when replicating identity columns in merge publications and transactional publications that support updatable subscriptions. For more information about replicating identity columns, see Replicating Identity Columns.

In most cases the default settings are appropriate, but they can be changed if an application requires different behavior. The main area to consider is triggers. For example, if you define an insert trigger with the NOT FOR REPLICATION option set, all user inserts fire the trigger, but inserts from replication agents do not. Consider a trigger that inserts data into a tracking table: when the user inserts the row originally, it is appropriate for the trigger to fire and enter a row into the tracking table, but the trigger should not fire when that data is replicated to the Subscriber, because it would result in an unnecessary row being inserted in the tracking table.

To specify the NOT FOR REPLICATION option

The NOT FOR REPLICATION option can be specified in the following ways: