3

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).

Improve this question

1 Answer 1

Reset to default
3

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".

Improve this answer
2
  • That's the fault of my thinking. The first impression I got was 'NOT FOR REPLICATION' meant the trigger wouldn't be propogated to the subscribers.
    – peter
    Commented Jun 7, 2012 at 23:04
  • I understand the error message above now. My identity column in the Audit table was set as NOT FOR REPLICATION and the merge agent was running the trigger (the second time the trigger would run) and trying to insert into the audit table without specifying the identity column value. The merge agent can't at that point create a new identity column value.
    – peter
    Commented Jun 7, 2012 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.