Merge replication and triggers?

Hello,
I hope someone can help me answer some questions about replication, to solve
my problems in a project. I'm involved in a webproject which uses a
replicated SQL Server 2000 database. The source database (which is created
by a 3rd party, so maintanance is not in our hands) contains triggers to
perform inserts into
child tables when something is inserted into a main table. Now we used merge
replication to get a copy on the server and keep its data in sync with our
local source db.
The problem is the following:
The installation of the Merge replication is done by another company and
they want to remove the triggers from the web database. They claim that
triggers are a problem for the replication. That creates a problem for our
webapplication since it's based on the logic of the triggers. So if they are
removed, the application
will no longer work properly.
I would like to know if someone has some experience doing something like we
do and if the triggers are a problem in the replication.
I see two solutions:
1. We make the triggers work with replication: problem solved
2. We remove the triggers on the webdatabase and implement their logic in
code: This involves a lot of additional work for us and creates a
maintanance problem when the trigger logic changes. Since that might happen
without notice, the website might stop working at a new release and data
might
become inconsisten. Not a pleasant thought.
I would like to hear how others dealt with this kind of problems
TIA,
Jeroen
In my experience having triggers should not in itself stop replication. The
only proviso to this is occasion the SQL Server parser can get a bit odd when
creating triggers using the replication process, which causes an error, but
one that can be rectified by hand.
As long as the triggers are set to 'Not For Replication' they work correctly.
Can I sugest that you ask for more details and how the triggers are causing
a problem, under what circumstances ect, perhaps they know something about
the database that you do not.
Peter
"Do not awake the sleeping dragon for you are crunchy and taste good with
ketchup".
Peter The Spate
"Jeroen" wrote:

> Hello,
> I hope someone can help me answer some questions about replication, to solve
> my problems in a project. I'm involved in a webproject which uses a
> replicated SQL Server 2000 database. The source database (which is created
> by a 3rd party, so maintanance is not in our hands) contains triggers to
> perform inserts into
> child tables when something is inserted into a main table. Now we used merge
> replication to get a copy on the server and keep its data in sync with our
> local source db.
> The problem is the following:
> The installation of the Merge replication is done by another company and
> they want to remove the triggers from the web database. They claim that
> triggers are a problem for the replication. That creates a problem for our
> webapplication since it's based on the logic of the triggers. So if they are
> removed, the application
> will no longer work properly.
> I would like to know if someone has some experience doing something like we
> do and if the triggers are a problem in the replication.
> I see two solutions:
> 1. We make the triggers work with replication: problem solved
> 2. We remove the triggers on the webdatabase and implement their logic in
> code: This involves a lot of additional work for us and creates a
> maintanance problem when the trigger logic changes. Since that might happen
> without notice, the website might stop working at a new release and data
> might
> become inconsisten. Not a pleasant thought.
> I would like to hear how others dealt with this kind of problems
> TIA,
> Jeroen
>
>
|||Hi Peter,
Thanks for answering. Good to hear that triggers should not be any problem
in itself. I will have to check that the triggers have the 'Not for
replication' option set. At this moment I do not have more details about the
problems that occured while setting up the replication, since another
company did that. I will contact them to see exactly what problems they
encountered while installing it.
But, as I read in your answer, my first solution should be possible if the
triggers have the correct options set, am I correct?
Thanks for your help,
Jeroen
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft. com> wrote in message
news:12FF5C8D-99BC-4929-B791-0EDE4E7A8604@.microsoft.com...[vbcol=seagreen]
> In my experience having triggers should not in itself stop replication.
> The
> only proviso to this is occasion the SQL Server parser can get a bit odd
> when
> creating triggers using the replication process, which causes an error,
> but
> one that can be rectified by hand.
> As long as the triggers are set to 'Not For Replication' they work
> correctly.
> Can I sugest that you ask for more details and how the triggers are
> causing
> a problem, under what circumstances ect, perhaps they know something about
> the database that you do not.
> Peter
> "Do not awake the sleeping dragon for you are crunchy and taste good with
> ketchup".
> Peter The Spate
>
> "Jeroen" wrote:
|||I would ask the other company for more details on what the problem with
replication and triggers is.
Merge replication has problems with triggers only when you update text and
image data type columns (refer to the section on Planning for Merge
Replication n BOL). As Peter had mentioned you might want to use the Not For
Replication clause on user triggers depending on what these triggers do -
For instance for audit triggers you might want them to have them fire if you
want to track changes made by the replication process.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeroen" <Noname@.noname.com> wrote in message
news:OcdnLxHJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I hope someone can help me answer some questions about replication, to
solve
> my problems in a project. I'm involved in a webproject which uses a
> replicated SQL Server 2000 database. The source database (which is created
> by a 3rd party, so maintanance is not in our hands) contains triggers to
> perform inserts into
> child tables when something is inserted into a main table. Now we used
merge
> replication to get a copy on the server and keep its data in sync with our
> local source db.
> The problem is the following:
> The installation of the Merge replication is done by another company and
> they want to remove the triggers from the web database. They claim that
> triggers are a problem for the replication. That creates a problem for our
> webapplication since it's based on the logic of the triggers. So if they
are
> removed, the application
> will no longer work properly.
> I would like to know if someone has some experience doing something like
we
> do and if the triggers are a problem in the replication.
> I see two solutions:
> 1. We make the triggers work with replication: problem solved
> 2. We remove the triggers on the webdatabase and implement their logic in
> code: This involves a lot of additional work for us and creates a
> maintanance problem when the trigger logic changes. Since that might
happen
> without notice, the website might stop working at a new release and data
> might
> become inconsisten. Not a pleasant thought.
> I would like to hear how others dealt with this kind of problems
> TIA,
> Jeroen
>