Can i replicate triggers in merge replication.
-
I have implemented merge replication with SQL server 2000 and i am not able to replicate triggers.
However, i have read at a few places that i should be able to do that in SQL server 2000. Does anyone know how to replicate the triggers.
Thanks!!
-
If you look at the properties of the publication, then look a the articles tab.
Select the article that the trigger is associated with and click the button with the ellipses (...). This will show you the Table Article Properties page.
On the Snapshot tab there is a section called 'Copy objects to destination' that has the option 'Use triggers'.
Select that option and you'll get you're triggers replicated.
If you are using scripts to generate you publication you need to change the @schema_option parameter for the sp_addmergearticle stored procedure.
You can find information about this in BOL
-
Thanks a lot for your reply.
I have the 'User Triggers' option already checked. Still no success. I will repeat the steps at my end.
1. Create a publication (merge replication) with 'User Triggers' option checked. The publication consists of just one table which has an insert,update trigger.
2. Push a subscription.
3. Let the publisher and subscriber sync for the first time.
4. Add a delete trigger for the above table at the publisher OR Change the insert/update trigger on the publisher.
After i complete step 4, the changes do not reflect in the subscriber after the next merge. I have also attached a snapshot of the Table Article Properties below for reference. I don't understand how is this possible. If the subscriber table is deleted and created again, how can it still get the old triggers.
Any help would be appreciated. Thanks!!
-
Another thing i would like to mention is that my publisher and subscriber are using MSDE SQL server 2000, if that matters.
Also, the table mentioned in the previous post is the only article in the publication. Do i have to publish any schema tables or anything ?
For the last 6 hours i am not able to figure this out
It sucks. -
Using MSDE should make no difference. As far as I know triggers are fully implemented in this version of SQL Server
Couldn't see your attachment so can't make any suggestions about that.
Question for you though - if you know what your triggers are supposed to look like, why are you trying to use replication to change them? Sure, they may need to change over time and this would be a nice way to distribute them, but generraly they'll be pretty static.
Before trying to merge again, did you re-run the snapshot? This works when you need to change permissions, but not sure if it will work with triggers.
Unfortunately, my experience here only lies in gettng rid of the unwanted triggers.
Hope you find the resolution.
Steve
-
Steve, Thanks for your reply.
I agree to your idea that my triggers will be fairly static. I need to change some triggers quite often. But i want to be able to use replication to distribute the changes. Duplicating the work usually is error prone.
What do you mean by re-running the snapshot ? May be i am not doing that. For testing purposes, my merge agent runs every minute and hence any changes on either end are applied to the other end every minute. Is there anything else apart from this that i need to do.?
-
Re-runnning the snapshot
In the Enterprise Manager under the replication monitor, you can drill down to see the Snapshot Agent for your publication. When you right click on that you have the option to start the agent. This may not help, but it is worth trying.
I wouldn't expect you should need it, but seeing as this situation is not working for you, it can't hurt to try.
Can't think of anything else for you to try at this stage. If that doesn't work, then I am as stuck as you are
-
I tried that. Did not help.
Does anyone else know how to fix this please?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply