replication and user triggers

Hello,
The SQL Server manual states you need special handling
when you have a user trigger (at publisher only in my
config) and immediate updating:
"If both the user-defined trigger and the immediate
updating trigger apply an update to the same row and you
have not included a subroutine for special case handling,
the transaction could terminate. Without special handling,
the update process continues in a loop with each trigger
update firing the other trigger until the maximum nesting
level (32) is reached and the transaction terminates. "
This is my problem. MS gives a solution in the online
manual, which I cannot get to work:
DECLARE @.retcode int, @.trigger_op char(10)
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.tablename sysname, @.trigger_op OUTPUT
IF @.retcode = 1 RETURN
A sample would be much appreciated, as the help does not
include the @.tablename parameter
for "sp_check_for_sync_trigger".
Tx
Todd
Todd,
I think this documentation is incorrect (as it is in a page on BOL -
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\rep
lsql.chm::/reploptions_4pdf.htm).
In the code for the system procedure you're calling, the correct argument
list is:
create proc sp_check_for_sync_trigger
(
@.tabid int,
@.trigger_op char(10) = NULL OUTPUT
)
You can get the tabid using the object_id() function. The output argument
returns ins, upd or del, depending on where this is called from.
HTH,
Paul Ibison
|||Paul, thank you.

>--Original Message--
>Todd,
>I think this documentation is incorrect (as it is in a
page on BOL -
>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
20Server\80\Tools\Books\rep
>lsql.chm::/reploptions_4pdf.htm).
>In the code for the system procedure you're calling, the
correct argument
>list is:
>create proc sp_check_for_sync_trigger
>(
> @.tabid int,
> @.trigger_op char(10) = NULL OUTPUT
>)
>You can get the tabid using the object_id() function. The
output argument
>returns ins, upd or del, depending on where this is
called from.
>HTH,
>Paul Ibison
>
>.
>
|||Hello,
I'm still having problems with the trigger continually
calling itself. I know this has something to do with
replication, because the triggers work fine before
enabling replication. I have a set of nested triggers, one
on a BANNER_TAB table, and the second on a BANNER_PARENT
table. The BANNER_TAB update trigger causes the
BANNER_PARENT trigger to fire, but then BANNER_PARENT gets
caught in a continous loop until it reaches the max 32
loops. The BANNER_PARENT update trigger is updating a
field that is part of the primary key. Here's my triggers,
any suggestions?
/*--*/
create trigger smis.tU_BANNER_TAB on smis.BANNER_TAB after
update NOT FOR REPLICATION
as
begin
SET NOCOUNT ON --required due to bug on VB recordset
update -- see Microsoft knowledgebase article 294160
/*check to see if replication immediate updating trigger
has fired the user trigger, if so, exit*/
DECLARE @.retcode int, @.table_id int, @.trigger_op char(10)
select @.table_id = object_id('BANNER_PARENT')
print 'BEGIN BANNER_TAB UPDATE'
print 'table_id = ' + cast(@.table_id as char(22))
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.trigger_op OUTPUT
print 'retcode = ' + CAST(@.retcode AS char(4))
print 'trigger_op = ' + @.trigger_op
IF @.retcode = 1 RETURN
/*start user trigger*/
declare @.numrows integer
declare @.old_banner_key integer
declare @.new_banner_key integer
select @.old_banner_key=banner_key from deleted
select @.new_banner_key=banner_key from inserted
if @.new_banner_key <> @.old_banner_key
begin
begin transaction
print 'Updating TRADE table banner key'
update smis.trade set banner_key=@.new_banner_key
where banner_key=@.old_banner_key
print 'Updating SITE_PRIORITY table banner key'
update smis.site_priority set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating DOTSUMMARY table banner key'
update smis.dotsummary set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating BAN_ATT table banner key'
update smis.ban_att set banner_key=@.new_banner_key
where banner_key=@.old_banner_key
print 'Updating ANALOGUE table banner key'
update smis.analogue set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating BANNER_PARENT table banner key'
update smis.banner_parent set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating STORE_MEASURE table banner key'
update smis.store_measure set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating PROPOSED table banner key'
update smis.proposed set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating ANA_STATUS table banner key'
update smis.ana_status set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating CONTRACT table banner key'
update smis.contract set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
commit transaction
end
if update(date_open)
begin
begin transaction
--if (select ban_status from deleted) = 'ACT'
--begin
-- synchronize first store_measure record with
date_open
if (select count(*) from smis.store_measure as
sm, inserted as i
where sm.banner_key=i.banner_key and
sm.date_updat=
(select min(date_updat)from
smis.store_measure
where banner_key=i.banner_key) and
sm.date_updat=i.date_open) = 0
begin
print 'Updating STORE_MEASURE table for
date_open'
update smis.STORE_MEASURE set
date_updat=b.date_open
from smis.store_measure sm,
smis.banner_tab b, inserted as i
where b.banner_key=i.banner_key
and b.banner_key=sm.banner_key
and sm.date_updat=(select min(date_updat)
from smis.store_measure
where banner_key=i.banner_key)
end
-- synchronize date_updat in banner_parent
if (select count(*)from smis.banner_parent as
bp, inserted as i
where bp.banner_key=i.banner_key and
bp.ban_parent_dt=
(select min(ban_parent_dt) from
smis.banner_parent
where banner_key=i.banner_key) and
bp.ban_parent_dt=i.date_open) = 0
begin
print 'Updating BANNER_PARENT table for
date_open'
update smis.banner_parent set
ban_parent_dt=b.date_open,date_updat=getdate()
from smis.banner_parent bp,banner_tab
b,inserted i
where b.banner_key=i.banner_key
and b.banner_key=bp.banner_key
and bp.ban_parent_dt=(select min
(ban_parent_dt) from smis.banner_parent
where banner_key=i.banner_key)
end
--end
commit transaction
end
print 'END BANNER_TAB UPDATE'
end
GO
/*--*/
create trigger smis.tU_BANNER_PARENT on smis.BANNER_PARENT
for update NOT FOR REPLICATION
as
begin
SET NOCOUNT ON --required due to bug on VB recordset
update -- see Microsoft knowledgebase article 294160
/*check to see if replication immediate updating trigger
has fired the user trigger, if so, exit*/
DECLARE @.retcode int, @.table_id int, @.trigger_op char(10)
select @.table_id = object_id('BANNER_PARENT')
print 'BEGIN BANNER_PARENT UPDATE'
print 'nest level = ' + cast(@.@.nestlevel as char(4))
print 'table_id = ' + cast(@.table_id as char(22))
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.trigger_op OUTPUT
print 'retcode = ' + CAST(@.retcode AS char(4))
print 'trigger_op = ' + @.trigger_op
IF @.retcode = 1 RETURN
/*start user trigger*/
declare @.cnt as smallint
-- test if the original date_updat is equal to the first
entry date_updat
select @.cnt = count(*) from smis.banner_parent bp
inner join inserted i on
bp.banner_key=i.banner_key where
bp.ban_parent_dt=(select min(ban_parent_dt) from
smis.banner_parent
where banner_key=i.banner_key) and
bp.ban_parent_dt=i.ban_parent_dt
if (@.cnt = 1)
begin
print 'There is only one parent company record --
resetting it to match the banner date open'
update smis.banner_parent set
ban_parent_dt=b.date_open
from smis.banner_parent bp, smis.banner_tab b,
inserted i2
where b.banner_key=bp.banner_key
and b.banner_key=i2.banner_key
and bp.ban_parent_dt=i2.ban_parent_dt
end
print 'END BANNER_PARENT UPDATE'
end
GO
|||Todd,
my suspicion is that there is an error in the stored procedure provided:
sp_check_for_sync_trigger. This procedure will work on the subscriber but
not the publisher. Even though it works on hte subscriber, the maximum
nesting level is reached on the publisher so the update still fails.
I have just tested this. In the code I noticed that it looks for names
starting with 'trg_MSsync_upd'; this type of trigger name applies to the
subscriber, but on the publisher the name is more like
"sp_MSsync_upd_trig_tTrigger_". I believe you'll have to create your own
version of this procedure and call it yourself to have this work. This is
actually quite easy if you look at the code, as you just need another three
queries built like the three provided, but with modified trigger names.
HTH,
Paul Ibison