Merge Replication: Insert Trigger ONLY when replicating

Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
Nest level check allows to avoid ping-pong traffic from subscriber to
publisher and vice versa
But in your case that should matter only if you are also replicating table
tblTest
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
|||Great. I'm not replicating this table. Thanks for the information.
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:eVz8BD8WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Nest level check allows to avoid ping-pong traffic from subscriber to
> publisher and vice versa
> But in your case that should matter only if you are also replicating table
> tblTest
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Christian Hamel" <chamel@.NOSPAM.com> wrote in message
> news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>
|||I think it is to prevent recursive triggers from causing duplicate entries
in msmerge_contents. I could be wrong here.
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
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>