Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
    
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 Jun 7 '12 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 Jun 7 '12 at 23:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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