Replicating Triggers

I am doing some analysis on merge replication and triggers for a project.

I have two tables, Product and Audit that I am replicating.

I have an update trigger on the Product table which is replicated. This trigger adds a row to the Audit table, which itself has an identity column.

On a client subscription I can update an item in the Product table, and it adds an item to the Audit table.

As soon as I sync the changes to the server I get an error,


A row update at 'ReplicatedDatabase' could not be propagated to
'ServerDatabase'. This failure can be caused by a constraint
violation. Explicit value must be specified for identity column in
table 'Audit' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity
column.

Am I right in thinking this error is because the trigger has fired at the client, and then again at the server when merged.

Is there a way to replicate a trigger to a client subscription, and not have it fire again when syncing to the server (if indeed this is what is happening)? My example here is not great, but our product will have times where triggers will need to fire at the client subscriptions.

The same triggers will need to fire at the server if data is updated directly on the server (not via replication).

I believe you want to mark the trigger NOT FOR REPLICATION. This will prevent the trigger from firing when the Merge Agent makes the update. Have a look at All about "Not for Replication".

StackExchange Database Administrators Q#18969, answer score: 3