all groups > sql server replication > august 2006 >
You're in the

sql server replication

group:

Did Replicati write/add the triggers to a replication table?



Did Replicati write/add the triggers to a replication table? Rich
8/4/2006 12:27:02 PM
sql server replication: Hello,

I took over a project several months ago. I am a basic Client/Server app
developer. I have experimented a little with replication and am currently
studying a book on the subject of replication, but otherwise have no real
industrial experience with replication. Today I just noticed some
triggers in a table, and I am pretty sure I recently recreated that table
(maybe I just copied it), and this table happens to be under replication.
There are 3 triggers for Delete, Insert, Update. They start as follows:

create trigger del_1368F74E47AA41ACB6FB5B4A168CD855 on [dbo].[WorkShop] FOR
DELETE AS
if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1
return
/* Declare variables */
declare @tablenick int, @retcode smallint, @reason nvarchar(255), @nickname
int, @lineage varbinary(255), @oldmaxversion int
....

create trigger ins_1368F74E47AA41ACB6FB5B4A168CD855 on [dbo].[WorkShop] for
insert as
if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1

....

create trigger upd_1368F74E47AA41ACB6FB5B4A168CD855 on [dbo].[WorkShop] FOR
UPDATE AS
if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1
....

Did Sql Server Replication add these triggers to the table or were they
manually added? The database I am working with is using Merge and Snapshot
replication - or is that Transactional and snapshop? I still get confused
between the expression Transactional Replication and Merge Replication.
There are some other tables in the database that are under replication but do
not contain triggers like these. Any explanations greatly appreciated.

Thanks,
Re: Did Replicati write/add the triggers to a replication table? Rich
8/4/2006 1:52:02 PM
Thanks very much for the explanation. I feel much better now.

Question: At what point do these triggers get created? Do they get created
when the Merge Replication is initially set up? Or is it possible for me to
drop the table and re-create it - and the Merge replication will re-create
the triggers?

Thanks,
Rich

[quoted text, click to view]
Re: Did Replicati write/add the triggers to a replication table? Paul Ibison
8/4/2006 9:39:59 PM
Rich,
these triggers are created by the merge process and are perfectly normal.
They are required to monitor changes to the merge replicated table and to
put details of these changes into the metadata tables msmerge_contents and
msmerge_tombstone.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Re: Did Replicati write/add the triggers to a replication table? Paul Ibison
8/5/2006 2:26:45 PM
Rich,
they get created during the initialization process. You can't remove them
without requiring reinitialization to occur afterwards, because some
metadata might have been missed and the data would be non-converged.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com



Re: Did Replicati write/add the triggers to a replication table? Rich
8/5/2006 7:26:02 PM
Hi Paul,

Thank you for that explanation. I guess I can't really drop a table that is
under replication (so I must have copied that table - minus the triggers).
But now I know not to mess with those triggers.

I will have to experiment creating merge replication tables/articles
(whatever the correct expression is - don't quite have it down yet).

May I ask what transactional replication refers to? Is it related to merge
replication or is it something different altogether?

Thanks,
Rich

[quoted text, click to view]
Re: Did Replicati write/add the triggers to a replication table? Paul Ibison
8/6/2006 12:00:00 AM
Rich - this is completely different. No triggers are involved and there is
another agent that reads the transaction log and writes relevant changes to
the distribution database. The distribution agent reads these transactions
and applies them to the subscriber. In its plain form, this is
unidirectional data flow. There are variations - immediate updating and
queued updating which make it bidirectional data flow. I'd recommend having
a look in BOL for plenty of details, and getting a copy of Hilary Cotters
book also.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Re: Did Replicati write/add the triggers to a replication table? Rich
8/7/2006 3:53:02 PM
Thanks. Yes, I have a copy of Hillary's book and reading it (slowly).

Rich

[quoted text, click to view]
Re: Did Replicati write/add the triggers to a replication table? Vikas
8/15/2006 4:33:01 AM
Hi Paul, went to this topic and got a question to ask? what is BOL and where
can i get that Hilary Cotters book?


--
Cheers



[quoted text, click to view]
Re: Did Replicati write/add the triggers to a replication table? Paul Ibison
8/15/2006 3:23:10 PM
Vikas - BOL is a newsgroup abbreviation for Books On Line - the help file
that comes with SQL Server.
Hilary's book should be available from Amazon in most countries (eg
http://www.amazon.com/gp/product/0974973602/sr=8-1/qid=1155651756/ref=pd_bbs_1/103-3811453-9913457?ie=UTF8).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

AddThis Social Bookmark Button