May 7, 2012

sp_MSaddmergetriggers_internal (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSaddmergetriggers_internal(nvarchar @source_table
, nvarchar @table_owner
, int @column_tracking
, tinyint @trigger_type
, nvarchar @viewname
, nvarchar @tsview
, nvarchar @trigname
, nvarchar @current_mappings_viewname
, nvarchar @past_mappings_viewname
, nvarchar @genhistory_viewname)

MetaData:

   
create procedure sys.sp_MSaddmergetriggers_internal
@source_table sysname, -- was type varchar(92), table name --
@table_owner sysname,
@column_tracking int, -- Is column tracking on - default is FALSE --
@trigger_type tinyint,
@viewname sysname,
@tsview sysname,
@trigname sysname,
@current_mappings_viewname sysname,
@past_mappings_viewname sysname,
@genhistory_viewname sysname
AS
set nocount on
declare @command nvarchar(max)
declare @command2 nvarchar(max)
declare @command3 nvarchar(max)
declare @command4 nvarchar(max)
declare @command5 nvarchar(max)
declare @inscommand nvarchar(2000)
declare @ifcoltracking nvarchar(255)
declare @tablenick int
declare @replnick binary(6)
declare @artid uniqueidentifier
declare @owner sysname
declare @site sysname
declare @db sysname
declare @object sysname
declare @tablenickchar nvarchar(11)
declare @retcode int
declare @bitmap varbinary(40)
declare @objid int
declare @notforrepl_str1 nvarchar(400)
declare @notforrepl_str2 nvarchar(400)
declare @notforrepl_bit bit
declare @rgcol sysname
declare @UnqualName sysname -- rightmost name node
declare @QualName1 sysname
declare @immediate_child_table_name nvarchar(258), @owner_qualified_immediate_child_table_name nvarchar(517), @immediate_child_objid int,
@join_filter_id int, @join_filterclause nvarchar(1000), @child_join_colname nvarchar(130)
declare @child_rgcol nvarchar(270)
declare @immediate_child_nickname int, @join_unique_key int, @child_expand_proc sysname, @immediate_child_partition_viewname nvarchar(258), @partition_deleted_view_rule nvarchar(max), @child_has_col_tracking int
declare @logical_record_view int, @logical_record_deleted_view_rule nvarchar(max), @logical_record_parent_nickname int
declare @lrp_partition_view_id int, @lrp_partition_view_name nvarchar(270), @lrp_pubid uniqueidentifier,
@lrp_publication_number smallint, @lrp_rgcol nvarchar(270), @lrp_objid int, @partition_options tinyint,
@is_top_level_logical_record_parent bit

declare @str_objid nvarchar(15)

declare @quoted_db_source_table_name nvarchar(776)
declare @quoted_source_table_name nvarchar(517)
declare @dbname nvarchar(258)
declare @delete_tracking bit
declare @max_colv_size_in_bytes int
declare @child_cannot_pre_exist bit
declare @parent_columns_unique bit
declare @gen_change_threshold int

-- the following is needed for auto identity range support
declare @identity_support int
declare @ident_increment numeric(38,0)
declare @is_publisher bit
declare @subid uniqueidentifier
declare @subidstr nvarchar(40)
declare @artidstr nvarchar(40)
declare @article_published bit
declare @level_generations_in_trigger bit
-- end auto identity range support variables

declare @quoted_trigname nvarchar(258)
declare @quoted_genhistory_viewname nvarchar(517)
declare @quoted_tsview nvarchar(517)
declare @quoted_viewname nvarchar(517)
declare @quoted_rgcol nvarchar(258)
declare @quoted_current_mappings_viewname nvarchar(517)
declare @quoted_past_mappings_viewname nvarchar(517)

-- Security check --
EXEC @retcode = dbo.sp_MSreplcheck_subscribe
if @@ERROR <> 0 or @retcode <> 0
return(1)

create table #tmptriggercmd (phase int NOT NULL, step int identity NOT NULL, cmdtext nvarchar(max) collate database_default null)

set @notforrepl_bit = 1
set @bitmap = 0x0
select @parent_columns_unique = 0

if @table_owner is NULL
select @table_owner = SCHEMA_NAME(schema_id) from sys.objects where object_id = object_id(@source_table)

select @quoted_source_table_name=QUOTENAME(@table_owner) + N'.' + QUOTENAME(@source_table)

set @objid = OBJECT_ID(@quoted_source_table_name)
set @str_objid = convert(nvarchar, @objid)

if exists (select * from dbo.sysmergearticles where objid = @objid and column_tracking = 1)
begin
declare @current_col_count int, @missing_col_count int
select @current_col_count = count(*)
from sys.columns where object_id = @objid
select @missing_col_count = coalesce((select max(missing_col_count)
from dbo.sysmergearticles where objid = @objid), 0)
-- 12 bytes per column in table + 1 trailing byte for colv.
select @max_colv_size_in_bytes = 12 * (@current_col_count + @missing_col_count) + 1
if @max_colv_size_in_bytes > 2953
select @max_colv_size_in_bytes = 2953
end
else
select @max_colv_size_in_bytes = 1

select @rgcol = name from sys.columns where object_id = @objid and is_rowguidcol = 1

if @source_table IS NULL
return 1

select @dbname = DB_NAME()

set @quoted_db_source_table_name=QUOTENAME(@dbname)+ N'.' + @quoted_source_table_name

select @owner = @table_owner
select @object = @source_table

select @quoted_trigname = quotename(@trigname)
select @quoted_genhistory_viewname = N'[dbo].' + quotename(@genhistory_viewname)
select @quoted_tsview = N'[dbo].' + quotename(@tsview)
select @quoted_viewname = N'[dbo].' + quotename(@viewname)
select @quoted_rgcol = quotename(@rgcol)
select @quoted_current_mappings_viewname = N'[dbo].' + quotename(@current_mappings_viewname)
select @quoted_past_mappings_viewname = N'[dbo].' + quotename(@past_mappings_viewname)

-- Initialize string for inserting to before_image table
exec sys.sp_MSgetbeforetableinsert @objid, @inscommand output

execute @retcode=sys.sp_MStablenickname @owner, @object, @tablenick output
if @retcode<>0 or @@ERROR<>0 return (1)

if exists (select * from dbo.sysmergearticles where nickname = @tablenick
and sys.fn_MSmerge_islocalpubid(pubid) = 1)
select @article_published = 1
else
select @article_published = 0

select @gen_change_threshold = min(isnull(generation_leveling_threshold,0)) from dbo.sysmergepublications
where pubid in (select pubid from dbo.sysmergearticles where nickname = @tablenick)
if @gen_change_threshold = 0 or @article_published = 0
select @level_generations_in_trigger = 0
else
select @level_generations_in_trigger = 1

exec @retcode= sys.sp_MSgetreplnick @replnick = @replnick out
if @retcode<>0 or @@error<>0
return 1


select @artid = artid, @delete_tracking = delete_tracking, @identity_support=identity_support,
@partition_options = partition_options
from dbo.sysmergepartitioninfoview where objid = @objid

-- check if this is the publisher or subscriber. Republishers are also considered as subscribers
if @identity_support = 1
begin
if exists (select a.artid, p.pubid from dbo.sysmergearticles a, dbo.sysmergepublications p
where a.artid=@artid and a.pubid=p.pubid and (sys.fn_MSmerge_islocalpubid(p.pubid)=0))
begin
-- the current db is either a republisher or a subscriber
select @is_publisher=0
-- the follow query attempts to get the subid and not the republished pubid or the republished publication's subids
select @subid=subid from dbo.MSmerge_identity_range
where is_pub_range=0 and artid=@artid and sys.fn_MSmerge_islocalsubid(subid)=1
end
else
begin
select @is_publisher=1
select @subid=subid from dbo.MSmerge_identity_range
where is_pub_range=0 and artid=@artid and sys.fn_MSmerge_islocalpubid(subid)=1
end
select @subidstr = N'''' + convert(nvarchar(36), @subid) + N''''
select @artidstr = N'''' + convert(nvarchar(36), @artid) + N''''
end

select @logical_record_view = logical_record_view, @logical_record_deleted_view_rule = logical_record_deleted_view_rule
from dbo.sysmergepartitioninfo where artid = @artid and logical_record_parent_nickname is not null

-- If column tracking wasn't passed in, just figure it out --
if @column_tracking is null
select @column_tracking = column_tracking from dbo.sysmergearticles where artid = @artid

select @tablenickchar = convert(nchar, @tablenick)

-- Check if the triggers can be made NOT FOR REPLICATION
if exists (select * from dbo.sysmergearticles
where nickname = @tablenick
and
(before_image_objid is not null or
before_view_objid is not null or
datalength (subset_filterclause) > 1
))
begin
select @notforrepl_bit = 0
end
else if exists (select * from dbo.sysmergesubsetfilters where art_nickname = @tablenick or join_nickname = @tablenick)
begin
select @notforrepl_bit = 0
end
else if exists (select * from dbo.sysmergearticles where nickname = @tablenick and pubid in (select pubid from dbo.sysmergepublications where use_partition_groups = 1))
begin
select @notforrepl_bit = 0
end
else
begin
select @notforrepl_bit = 1
end

if exists (select * from dbo.sysmergepartitioninfoview
where artid = @artid
and partition_options = 2
and sys.fn_MSmerge_islocalpubid(pubid) = 0)
and exists (select * from dbo.sysmergepartitioninfoview
where artid = @artid
and partition_options = 2
and sys.fn_MSmerge_islocalpubid(pubid) = 1)
begin
-- republisher of multiple-hop well-partitioned articles (partition based filtering)
select @notforrepl_str1 = ' '
select @notforrepl_str2 = '
if (select trigger_nestlevel()) = 1 and @is_mergeagent = 1
return '

end
else if @partition_options = 2 or @partition_options = 3
begin
select @notforrepl_bit = 1
select @notforrepl_str1 = ' not for replication '
select @notforrepl_str2 = ' '
end
else
begin
select @notforrepl_str1 = ' '
select @notforrepl_str2 = '
if (select trigger_nestlevel()) = 1 and @is_mergeagent = 1
return '

end

-- If column tracking is on, construct the string to initialize colv's
if (@column_tracking <> 0)
select @ifcoltracking = ' set @colv1 = 0xFF'
else
select @ifcoltracking = ' set @colv1 = NULL'

select @is_top_level_logical_record_parent = 0

if @logical_record_view is not null
begin
select top 1 @logical_record_parent_nickname = logical_record_parent_nickname
from dbo.sysmergepartitioninfo
where artid = @artid
and logical_record_parent_nickname is not null

if @logical_record_parent_nickname = @tablenick
select @is_top_level_logical_record_parent = 1
end

-- ins_ trigger generation
if @trigger_type = 0
begin
-- UNDONE maybe remove null guid checks in SQL SERVER 7.0
select @command = '
create trigger '
+ @quoted_trigname + ' on ' + @quoted_db_source_table_name +
' for insert ' + @notforrepl_str1 + ' as
declare @is_mergeagent bit, @at_publisher bit, @retcode smallint

set rowcount 0
set transaction isolation level read committed
'


if exists (select * from dbo.sysmergearticles where nickname = @tablenick
and (pubid in (select pubid from dbo.sysmergepublications where use_partition_groups = 1)
or published_in_tran_pub = 1))
begin
select @command = @command + '
exec @retcode = sys.sp_MSisreplmergeagent @is_mergeagent output, @at_publisher output
if @@error <> 0 or @retcode <> 0
begin
rollback tran
return
end '

end
else
begin
select @command = @command + '
select @is_mergeagent = convert(bit, sessionproperty('
'replication_agent''))
select @at_publisher = 0 '

end

if not exists (select * from dbo.sysmergearticles where nickname = @tablenick and pubid in (select pubid from dbo.sysmergepublications where use_partition_groups = 1))
and not exists (select * from dbo.sysmergepartitioninfo where artid = @artid and logical_record_view is not null)
select @command = @command + @notforrepl_str2

if @partition_options = 2 or @partition_options = 3
select @command = @command + '
if @is_mergeagent = 1 and @at_publisher = 1
return '


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)
select @command=''

-- perform identity range check here.
if @identity_support = 1
begin
select @ident_increment = IDENT_INCR(@quoted_source_table_name)
select @command = '
if is_member('
'db_owner'') = 1
begin
-- select the range values from the MSmerge_identity_range table
-- this can be hardcoded if performance is a problem
declare @range_begin numeric(38,0)
declare @range_end numeric(38,0)
declare @next_range_begin numeric(38,0)
declare @next_range_end numeric(38,0)

select @range_begin = range_begin,
@range_end = range_end,
@next_range_begin = next_range_begin,
@next_range_end = next_range_end
from dbo.MSmerge_identity_range where artid='
+ @artidstr + ' and subid=' + @subidstr + ' and is_pub_range=0

if @range_begin is not null and @range_end is not NULL and @next_range_begin is not null and @next_range_end is not NULL
begin'


if @is_publisher=1
begin
-- if it is the publisher when both current range and next range are full we will
-- call a proc to refresh the range
select @command = @command + '
if IDENT_CURRENT('
'' + sys.fn_replreplacesinglequote(@quoted_source_table_name) + ''') = @range_end
begin
DBCC CHECKIDENT ('
'' + sys.fn_replreplacesinglequote(@quoted_source_table_name) + ''', RESEED, @next_range_begin) with no_infomsgs
end'


if @ident_increment > 0
select @command = @command + '
else if IDENT_CURRENT('
'' + sys.fn_replreplacesinglequote(@quoted_source_table_name) + ''') >= @next_range_end'
else
select @command = @command + '
else if IDENT_CURRENT('
'' + sys.fn_replreplacesinglequote(@quoted_source_table_name) + ''') <= @next_range_end'

select @command = @command + '
begin
exec sys.sp_MSrefresh_publisher_idrange '
'' + sys.fn_replreplacesinglequote(@quoted_source_table_name) + ''', ' + @subidstr + ', ' + @artidstr + ', 2, 1
if @@error<>0 or @retcode<>0
goto FAILURE
end'

end
else
begin
-- on the subscriber when both ranges are full we cannot do anything. so only do a
-- reseed when range_end is reached.
select @command = @command + '
if IDENT_CURRENT('
'' + sys.fn_replreplacesinglequote(@quoted_source_table_name) + ''') = @range_end
begin
DBCC CHECKIDENT ('
'' + sys.fn_replreplacesinglequote(@quoted_source_table_name) + ''', RESEED, @next_range_begin) with no_infomsgs
end'

end
select @command = @command + '
end
end'

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)
select @command=''
end


select @command = @command + '
declare @article_rows_inserted int
select @article_rows_inserted = count(*) from inserted
if @article_rows_inserted = 0
return
declare @tablenick int, @rowguid uniqueidentifier
, @replnick binary(6), @lineage varbinary(311), @colv1 varbinary('
+ convert(nvarchar(13), @max_colv_size_in_bytes) + '), @cv varbinary(1)
, @ccols int, @newgen bigint, @version int, @curversion int
, @oldmaxversion int, @child_newgen bigint, @child_oldmaxversion int, @child_metadatarows_updated int
, @logical_record_parent_rowguid uniqueidentifier
, @num_parent_rows int, @parent_row_inserted bit, @ts_rows_exist bit, @marker uniqueidentifier
declare @dt datetime
declare @nickbin varbinary(8)
declare @error int'


if @logical_record_view is not null and @is_top_level_logical_record_parent = 0
select @command = @command + '
declare @logical_record_distinct_parent_rowguids table (logical_record_parent_rowguid uniqueidentifier unique) '


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
set nocount on
set @tablenick = '
+ @tablenickchar + '
set @lineage = 0x0
set @retcode = 0
select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick
select @dt = getdate()

select @replnick = '
+ sys.fn_varbintohexstr(@replnick) + '
set @nickbin= @replnick + 0xFF

select @newgen = NULL
select top 1 @newgen = generation from '
+ @quoted_genhistory_viewname + ' with (rowlock, updlock, readpast)
where art_nick = '
+ @tablenickchar + ' and genstatus = 0'
if @level_generations_in_trigger = 1
select @command = @command + '
and changecount <= ('
+ convert(nvarchar, @gen_change_threshold) + ' - isnull(@article_rows_inserted,0))'
select @command = @command + '
if @newgen is NULL
begin
insert into '
+ @quoted_genhistory_viewname + ' with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, @tablenick, @nickbin, @dt, @article_rows_inserted)
select @error = @@error, @newgen = @@identity
if @error<>0 or @newgen is NULL
goto FAILURE
end'

if @level_generations_in_trigger = 1
select @command = @command + '
else
begin
-- now update the changecount of the generation we go to reflect the number of rows we put in this generation
update '
+ @quoted_genhistory_viewname + ' with (rowlock)
set changecount = changecount + @article_rows_inserted
where generation = @newgen
if @@error<>0 goto FAILURE
end'


select @command = @command + '
set @lineage = { fn UPDATELINEAGE (0x0, @replnick, 1) }
'
+ @ifcoltracking + '
if (@@error <> 0)
begin
goto FAILURE
end

select @ts_rows_exist = 0'

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

if @logical_record_view is not null
select @command ='
if @article_rows_inserted = 1
begin
select @rowguid = rowguidcol from inserted
select @ts_rows_exist = 1 where exists (select rowguid from '
+ @quoted_tsview + ' where tablenick = @tablenick and rowguid = @rowguid)
end
else
begin
select @ts_rows_exist = 1 where exists (select ts.rowguid from inserted i, '
+ @quoted_tsview + ' ts with (rowlock) where ts.tablenick = @tablenick and ts.rowguid = i.rowguidcol)
end'

else
select @command ='
select @ts_rows_exist = 1 where exists (select ts.rowguid from inserted i, '
+ @quoted_tsview + ' ts with (rowlock) where ts.tablenick = @tablenick and ts.rowguid = i.rowguidcol)'
insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
if @ts_rows_exist = 1
begin'

if @logical_record_view is not null
select @command = @command + '
if @article_rows_inserted = 1
select @version = max({fn GETMAXVERSION(lineage)}) from '
+ @quoted_tsview + ' where
tablenick = @tablenick and rowguid = @rowguid
else'

select @command = @command + '
select @version = max({fn GETMAXVERSION(lineage)}) from '
+ @quoted_tsview + ' where
tablenick = @tablenick and rowguid in (select rowguidcol from inserted)

if @version is not null
begin
-- reset lineage and colv to higher version...
set @curversion = 0
while (@curversion <= @version)
begin
set @lineage = { fn UPDATELINEAGE (@lineage, @replnick, @oldmaxversion+1) }
set @curversion= { fn GETMAXVERSION(@lineage) }
end

if (@colv1 IS NOT NULL)
set @colv1 = { fn UPDATECOLVBM(@colv1, @replnick, 0x01, 0x00, { fn GETMAXVERSION(@lineage) }) }'


if @logical_record_view is not null
select @command = @command + '
if @article_rows_inserted = 1
delete from '
+ @quoted_tsview + ' with (rowlock) where tablenick = @tablenick and rowguid = @rowguid
else'


select @command = @command + '
delete from '
+ @quoted_tsview + ' with (rowlock) where tablenick = @tablenick and rowguid in
(select rowguidcol from inserted)
end
end
select @marker = newid() '

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

if @logical_record_view is not null
begin
select @command = '
if (@article_rows_inserted = 1)
begin
declare @contents_row_exists bit

select @contents_row_exists = 0

select @contents_row_exists = 1 where exists (select rowguid from '
+ @quoted_viewname + ' with (rowlock) where tablenick = @tablenick and rowguid = @rowguid) '

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

if @is_top_level_logical_record_parent = 0
begin

select @command = '

select @logical_record_parent_rowguid = logical_record_parent_rowguid
from '
+ quotename(object_name(@logical_record_view)) + ' where ' + @quoted_rgcol + ' = @rowguid '

select @command = @command + '
if @contents_row_exists = 0
begin '


select @command = @command + '
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid, marker)
values (@tablenick, @rowguid, @lineage, @colv1, @newgen, (-@newgen), @logical_record_parent_rowguid, @marker) '


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
end
else
begin
update '
+ @quoted_viewname + ' set logical_record_parent_rowguid = @logical_record_parent_rowguid
where tablenick = @tablenick and rowguid = @rowguid

end
exec @retcode = sys.sp_MSupdate_singlelogicalrecordmetadata '
+ convert(nvarchar, @logical_record_parent_nickname) + ', @logical_record_parent_rowguid, @replnick, @parent_row_inserted output
if @@error <> 0 or @retcode <> 0
goto FAILURE
end
else
begin '

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)
end -- @is_top_level_logical_record_parent = 1
else
begin
select @command = '
if @contents_row_exists = 0
begin
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid, marker, logical_record_lineage)
values (@tablenick, @rowguid, @lineage, @colv1, @newgen, (-@newgen), @rowguid, @marker, @lineage) '


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
end
else
begin
update '
+ @quoted_viewname + ' set logical_record_parent_rowguid = @rowguid,
logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @oldmaxversion+1) }
where tablenick = @tablenick and rowguid = @rowguid
end
end
else
begin '

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)
end
end

if @logical_record_view is null
begin
select @command = '
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, marker)
select @tablenick, rowguidcol, @lineage, @colv1, @newgen, (-@newgen), @marker
from inserted i where not exists
(select rowguid from '
+ @quoted_viewname + ' with (readcommitted, rowlock, readpast) where tablenick = @tablenick and rowguid = i.rowguidcol) '

end
else
begin
if @is_top_level_logical_record_parent = 0
select @command = '
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid, marker)
select @tablenick, i.rowguidcol, @lineage, @colv1, @newgen, (-@newgen), logical_record_parent_rowguid, @marker
from inserted i, '
+ quotename(object_name(@logical_record_view)) + ' lrv with (rowlock)
where i.rowguidcol = lrv.'
+ @quoted_rgcol + '
and not exists
(select rowguid from '
+ @quoted_viewname + ' with (readcommitted, rowlock, readpast) where tablenick = @tablenick and rowguid = i.rowguidcol)

end'

else -- @is_top_level_logical_record_parent = 1
select @command = '
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid, marker, logical_record_lineage)
select @tablenick, i.rowguidcol, @lineage, @colv1, @newgen, (-@newgen), i.rowguidcol, @marker,@lineage
from inserted i
where not exists
(select rowguid from '
+ @quoted_viewname + ' with (readcommitted, rowlock, readpast) where tablenick = @tablenick and rowguid = i.rowguidcol)
end'

end

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
if @@rowcount = 0
begin
select top 1 @marker = mc.marker
from inserted i, '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = @tablenick
and mc.rowguid = i.rowguidcol

end '


-- uncomment for compplan size test
-- insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

if @logical_record_view is not null and @is_top_level_logical_record_parent = 0
begin
select @command = '
if @article_rows_inserted > 1
begin
insert into @logical_record_distinct_parent_rowguids
select distinct lrv.logical_record_parent_rowguid
from inserted i, '

+ quotename(object_name(@logical_record_view)) + ' lrv with (rowlock)
where i.rowguidcol = lrv.'
+ @quoted_rgcol + '
and lrv.logical_record_parent_rowguid is not null

select @num_parent_rows = @@rowcount

if @num_parent_rows = 1
begin
-- this could happen when all rows being inserted are the children of the same parent row.
select @logical_record_parent_rowguid = logical_record_parent_rowguid from @logical_record_distinct_parent_rowguids
exec @retcode = sys.sp_MSupdate_singlelogicalrecordmetadata '
+ convert(nvarchar, @logical_record_parent_nickname) + ', @logical_record_parent_rowguid, @replnick, @parent_row_inserted output
if @@error <> 0 or @retcode <> 0
goto FAILURE
end
else if @num_parent_rows > 1
begin
declare @logical_record_parent_oldmaxversion int, @logical_record_lineage varbinary(311), @logical_record_parent_regular_lineage varbinary(311), @logical_record_parent_gencur bigint

select top 1 @logical_record_parent_oldmaxversion = maxversion_at_cleanup
from dbo.sysmergearticles
where nickname = '
+ convert(nvarchar,@logical_record_parent_nickname) + '

-- the code below will get an open generation for the parent
select @logical_record_parent_gencur = NULL
select top 1 @logical_record_parent_gencur = generation from '
+ @quoted_genhistory_viewname + ' with (rowlock, updlock, readpast)
where art_nick = '
+ convert(nvarchar,@logical_record_parent_nickname) + '
and genstatus = 0'

if @level_generations_in_trigger = 1
select @command = @command + '
and changecount <= ('
+ convert(nvarchar, @gen_change_threshold) + '-isnull(@num_parent_rows,0))'
select @command = @command + '
if @logical_record_parent_gencur is NULL
begin
insert into '
+ @quoted_genhistory_viewname + ' with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, '
+ convert(nvarchar,@logical_record_parent_nickname) + ', @nickbin, @dt, @num_parent_rows)
select @error= @@error, @logical_record_parent_gencur = @@identity
if @error<>0 or @logical_record_parent_gencur is NULL
goto FAILURE
end'

if @level_generations_in_trigger = 1
select @command = @command + '
else
begin
-- do the update right away to change the changecount to include the rows that we just put in the generation
update '
+ @quoted_genhistory_viewname + ' with (rowlock)
set changecount = changecount + @num_parent_rows
where generation = @logical_record_parent_gencur
if @@error<>0 goto FAILURE
end'


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
update '
+ @quoted_viewname + ' with (rowlock)
set logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @logical_record_parent_oldmaxversion+1) },
generation = @logical_record_parent_gencur
from @logical_record_distinct_parent_rowguids lrpg join '
+ @quoted_viewname + ' with (rowlock)
on tablenick = '
+ convert(nvarchar, @logical_record_parent_nickname) + '
and rowguid = lrpg.logical_record_parent_rowguid
option (force order, loop join)

delete from @logical_record_distinct_parent_rowguids
where logical_record_parent_rowguid in (select rowguid from '
+ @quoted_viewname + '
where tablenick = '
+ convert(nvarchar, @logical_record_parent_nickname) + ')

-- Now @logical_record_distinct_parent_rowguids is left with parent rowguids that are not present in MSmerge_contents.
if exists (select * from @logical_record_distinct_parent_rowguids)
begin
select @logical_record_lineage = { fn UPDATELINEAGE(0x0, @replnick, @logical_record_parent_oldmaxversion+1) }

-- if no cleanup done yet, use 1 as the version.
if @logical_record_parent_oldmaxversion = 1
select @logical_record_parent_regular_lineage = { fn UPDATELINEAGE(0x0, @replnick, 1) }
else
select @logical_record_parent_regular_lineage = @logical_record_lineage

insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen,
logical_record_parent_rowguid, logical_record_lineage)
select distinct '
+ convert(nvarchar, @logical_record_parent_nickname) + ', lrpg.logical_record_parent_rowguid,
@logical_record_parent_regular_lineage, 0x00, @logical_record_parent_gencur, NULL, lrpg.logical_record_parent_rowguid,
@logical_record_lineage
from @logical_record_distinct_parent_rowguids lrpg
where not exists (select rowguid from '
+ @quoted_viewname + ' with (rowlock) where tablenick = ' + convert(nvarchar, @logical_record_parent_nickname) + '
and rowguid = lrpg.logical_record_parent_rowguid)
end
end
end'

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)
end

select @command = '
if @@error <> 0
goto FAILURE '


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

-- if partition groups functionality is being used, we need to insert the partition ids for these
-- newly inserted contents rows.

if @logical_record_view is not null and @is_top_level_logical_record_parent = 0
begin
declare lrp_partition_view_ids CURSOR LOCAL FAST_FORWARD FOR
select partition_view_id, pubid, objid
from dbo.sysmergepartitioninfoview where nickname = @logical_record_parent_nickname
and partition_view_id is not null
for read only

open lrp_partition_view_ids

fetch next from lrp_partition_view_ids into @lrp_partition_view_id, @lrp_pubid, @lrp_objid

while (@@fetch_status <> -1)
begin

select top 1 @lrp_publication_number = publication_number from dbo.sysmergepublications where pubid = @lrp_pubid
select @lrp_rgcol = quotename(name) from sys.columns where object_id = @lrp_objid and is_rowguidcol = 1
select @lrp_partition_view_name = 'dbo.' + quotename(object_name(@lrp_partition_view_id))
if @lrp_partition_view_name is not null
begin

select @command = NULL
select @command = '

if @article_rows_inserted = 1 or @num_parent_rows = 1
begin
-- if we did not insert the parent row, we have no business inserting the mapping row.
if @parent_row_inserted = 1
begin
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @lrp_publication_number) + ', '
+ convert(nvarchar,@logical_record_parent_nickname) + ', v.' + @lrp_rgcol + ', v.partition_id
from '
+ @lrp_partition_view_name + ' as v with (rowlock)
where v.'
+ @lrp_rgcol + ' = @logical_record_parent_rowguid
end
end
else
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @lrp_publication_number) + ', '
+ convert(nvarchar,@logical_record_parent_nickname) + ', v.' + @lrp_rgcol + ', v.partition_id
from @logical_record_distinct_parent_rowguids as lrpg, '

+ @lrp_partition_view_name + ' as v with (rowlock)
where v.'
+ @lrp_rgcol + ' = lrpg.logical_record_parent_rowguid
and not exists (select * from '
+ @quoted_current_mappings_viewname + ' mcpg with (readcommitted, rowlock, readpast)
where mcpg.tablenick = '
+ convert(nvarchar,@logical_record_parent_nickname) + '
and mcpg.rowguid = v.'
+ @lrp_rgcol + ')
'


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)
end

fetch next from lrp_partition_view_ids into @lrp_partition_view_id, @lrp_pubid, @lrp_objid
end

close lrp_partition_view_ids
deallocate lrp_partition_view_ids
end

declare @partition_view_id int, @partition_view_name nvarchar(270), @pubid uniqueidentifier, @partition_inserted_view_rule nvarchar(max),
@publication_number smallint, @basetable_ownerqualified_replviewname nvarchar(517)

if @article_published = 1
begin
declare partition_view_ids CURSOR LOCAL FAST_FORWARD FOR
select partition_view_id, pubid, partition_inserted_view_rule, partition_options
from dbo.sysmergepartitioninfoview where nickname = @tablenick
and partition_view_id is not null
for read only

open partition_view_ids

fetch next from partition_view_ids into @partition_view_id, @pubid, @partition_inserted_view_rule, @partition_options

while (@@fetch_status <> -1)
begin

select top 1 @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid

select @basetable_ownerqualified_replviewname =
quotename(SCHEMA_NAME(schema_id)) + N'.' + quotename(object_name(object_id))
from sys.objects with (nolock) where object_id =
(select top 1 repl_view_id from dbo.sysmergepartitioninfoview
where pubid = @pubid and objid = @objid)

select @partition_view_name = 'dbo.' + quotename(object_name(@partition_view_id))
if @partition_view_name is not null
begin

select @command3 = NULL
if @partition_options = 2 or @partition_options = 3
select @command3 = '
if @is_mergeagent = 0 or @at_publisher = 0 '


select @command3 = isnull(@command3, ' ')

if @logical_record_view is not null
select @command3 = @command3 + '
if @article_rows_inserted = 1
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @publication_number) + ', @tablenick, @rowguid, v.partition_id
from '
+ @partition_view_name + ' as v with (rowlock)
where v.'
+ @quoted_rgcol + ' = @rowguid
else'


select @command3 = @command3 + '
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @publication_number) + ', @tablenick, v.' + @quoted_rgcol + ', v.partition_id
from ( '
+ @partition_inserted_view_rule + ' ) as v '

insert into #tmptriggercmd (phase, cmdtext) values (3, @command3)
end

fetch next from partition_view_ids into @partition_view_id, @pubid, @partition_inserted_view_rule, @partition_options
end

close partition_view_ids
deallocate partition_view_ids

-- Logical records are based on PK-FK relationships. So when a parent row is inserted, it can be safely
-- assumed that none of the child rows would pre-exist. As a result, the following expansion code will not
-- find any children if logical records are in use. So, don't need to worry about determining the
-- logical_record_parent_rowguid for the child rows brought in via expansion below.

declare @expand_proc nvarchar(255)

declare expand_procs CURSOR LOCAL FAST_FORWARD FOR
select distinct expand_proc from dbo.sysmergepartitioninfoview
where nickname = @tablenick
and expand_proc is not null
and exists -- at least one non-LR child exists
(select * from dbo.sysmergesubsetfilters
where join_nickname = @tablenick
and filter_type = 1)

open expand_procs

fetch next from expand_procs into @expand_proc

while @@fetch_status <> -1
begin
set @command3 = '

exec @retcode = dbo.'
+ quotename(@expand_proc) + ' @marker = @marker, @inherit_pastchanges_generation = -1, @trigger_type = 0
if @retcode <> 0 goto FAILURE '

-- uncomment for compplan size tests
-- insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

fetch next from expand_procs into @expand_proc
end

close expand_procs
deallocate expand_procs

declare immediate_children CURSOR LOCAL FAST_FORWARD FOR
select distinct quotename(object_name(sma.objid)), sma.objid, sma.nickname, 'dbo.' + quotename(object_name(sma.partition_view_id)),
ssf.join_filterid, ssf.join_filterclause, ssf.join_unique_key, sma.expand_proc, sma.pubid, sma.column_tracking
from dbo.sysmergesubsetfilters ssf, dbo.sysmergepartitioninfoview sma where ssf.join_nickname = @tablenick
and sma.nickname = ssf.art_nickname
and sma.pubid = ssf.pubid
and ssf.filter_type = 1
and sma.partition_view_id is not null
for read only

open immediate_children

fetch next from immediate_children into @immediate_child_table_name, @immediate_child_objid, @immediate_child_nickname,
@immediate_child_partition_viewname, @join_filter_id, @join_filterclause, @join_unique_key, @child_expand_proc, @pubid, @child_has_col_tracking

if (@@fetch_status <> -1)
begin
select @command3 = '

declare @child_marker uniqueidentifier
declare @child_rowcount int
set @child_marker = newid() '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
end

while (@@fetch_status <> -1)
begin

exec @retcode = sys.sp_MSis_joinfilter_based_on_PK_UQ_constraints @pubid = @pubid,
@join_filter_id = @join_filter_id,
@dri_based = @child_cannot_pre_exist output,
@unique_constraint_based = @parent_columns_unique output

if @@error <> 0 or @retcode <> 0
return 1

if @child_cannot_pre_exist = 1
goto InsertTriggerGetNextChild

select @owner_qualified_immediate_child_table_name =
quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(object_name(object_id))
from sys.objects with (nolock) where object_id =
(select top 1 repl_view_id from dbo.sysmergepartitioninfoview
where pubid = @pubid and objid = @immediate_child_objid)

select top 1 @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid

-- Fix for VSTS 217316
select top 1 @partition_inserted_view_rule = partition_inserted_view_rule from dbo.sysmergepartitioninfoview where pubid = @pubid and artid = @artid

select top 1 @child_join_colname = name from sys.columns where object_id = @immediate_child_objid
and sys.fn_MSisfilteredcolumn(@join_filterclause, name, @immediate_child_objid) = 1

select @child_rgcol = quotename(name) from sys.columns where object_id = @immediate_child_objid and is_rowguidcol = 1

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
if @logical_record_view is not null
select @command3 = '
if @article_rows_inserted = 1
select @child_rowcount = count(*) from '

+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from '
+ @basetable_ownerqualified_replviewname + ' ' + quotename(object_name(@objid)) + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ quotename(object_name(@objid)) + '.' + @quoted_rgcol + ' = @rowguid and ' + '(' + @join_filterclause + ')' + ')
else'

else
select @command3 = ''
select @command3 = @command3 + '
select @child_rowcount = count(*) from '

+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from '
+ ' inserted ' + quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + ')
'

end
else
begin
if @logical_record_view is not null
select @command3 = '
if @article_rows_inserted = 1
select @child_rowcount = count(*) from '

+ @basetable_ownerqualified_replviewname + ' ' + quotename(object_name(@objid)) + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ quotename(object_name(@objid)) + '.' + @quoted_rgcol + ' = @rowguid and ' + '(' + @join_filterclause + ')
else'

else
select @command3 = ''
select @command3 = @command3 + '
select @child_rowcount = count(*) from '

+ ' inserted ' + quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')
'

end
insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

-- touch the immediate children of deleted rows
select @command3 = '
if @child_rowcount > 0
begin
select @child_oldmaxversion= maxversion_at_cleanup
from dbo.sysmergearticles
where nickname = '
+ convert(nvarchar, @immediate_child_nickname) + '

-- the code below will get an open generation for the child article
select @child_newgen = NULL
select top 1 @child_newgen = generation from '
+ @quoted_genhistory_viewname + ' with (rowlock, updlock, readpast)
where art_nick = '
+ convert(nvarchar,@immediate_child_nickname) + '
and genstatus = 0'

if @level_generations_in_trigger = 1
select @command3 = @command3 + '
and changecount <= ('
+ convert(nvarchar, @gen_change_threshold) + '- isnull(@child_rowcount,0))'
select @command3 = @command3 + '
if @child_newgen is NULL
begin
insert into '
+ @quoted_genhistory_viewname + ' with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, '
+ convert(nvarchar,@immediate_child_nickname) + ', @nickbin, @dt, @child_rowcount)
select @error=@@error, @child_newgen = @@identity
if @error<>0 or @child_newgen is NULL
goto FAILURE
end'

if @level_generations_in_trigger = 1
select @command3 = @command3 + '
else
begin
-- do the update right away to change the changecount to include the rows that we just put in the generation
update '
+ @quoted_genhistory_viewname + ' with (rowlock)
set changecount = changecount + @child_rowcount
where generation = @child_newgen
if @@error<>0 goto FAILURE
end'

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
if @logical_record_view is not null
begin
select @command3 = '
if @article_rows_inserted = 1
update '
+ @quoted_viewname + ' with (rowlock) set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker
from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and mc.rowguid in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from '
+ @basetable_ownerqualified_replviewname + ' ' + quotename(object_name(@objid)) + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ quotename(object_name(@objid)) + '.'
+ @quoted_rgcol + ' = @rowguid and '
+ '(' + @join_filterclause + ')' + ')
else'

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
end
else
select @command3 = ''

select @command3 = '
update '
+ @quoted_viewname + ' with (rowlock) set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker
from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and mc.rowguid in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from
inserted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + ') '
end
else
begin
if @logical_record_view is not null
begin
select @command3 = '
if @article_rows_inserted = 1
update '
+ @quoted_viewname + ' with (rowlock) set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker
from '

+ @basetable_ownerqualified_replviewname + ' ' + quotename(object_name(@objid)) + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock), '
+ @quoted_viewname + ' mc with (rowlock)
where '
+ quotename(object_name(@objid)) + '.' + @quoted_rgcol + ' = @rowguid and (' + @join_filterclause + ') and
mc.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and mc.rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + '
else'

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
end
else
select @command3 = ''
select @command3 = '
update '
+ @quoted_viewname + ' with (rowlock) set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker
from
inserted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock), '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and mc.rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + '
and '
+ '(' + @join_filterclause + ') '
end

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '

select @child_metadatarows_updated = @@rowcount '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '
if @child_metadatarows_updated < @child_rowcount '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

set @command3 = '
begin
set @lineage = 0x00 '


insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

if @child_has_col_tracking = 1
set @command3 = '
set @cv = 0xFF '

else
set @command3 = '
set @cv = NULL '


insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
if @logical_record_view is not null
set @command3 = '
if @article_rows_inserted = 1
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, generation, lineage, colv1, marker)
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', @child_newgen, @lineage, @cv, @child_marker
from '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from '
+ @basetable_ownerqualified_replviewname + ' ' + quotename(object_name(@objid)) + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ quotename(object_name(@objid)) + '.' + @quoted_rgcol + ' = @rowguid and ' + '(' + @join_filterclause + ')' + ')
and not exists
(select * from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert(nvarchar, @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + ')
else'

else
select @command3 = ''
select @command3 = @command3 + '
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, generation, lineage, colv1, marker)
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', 0, @lineage, @cv, @child_marker
from '

+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from
inserted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + ')
and not exists
(select * from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert(nvarchar, @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + ')
'

end
else
begin
if @logical_record_view is not null
set @command3 = '
if @article_rows_inserted = 1
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, generation, lineage, colv1, marker)
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', 0, @lineage, @cv, @child_marker
from '

+ @basetable_ownerqualified_replviewname + ' ' + quotename(object_name(@objid)) + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ quotename(object_name(@objid)) + '.' + @quoted_rgcol + ' = @rowguid and ' + '(' + @join_filterclause + ')' + '
and not exists
(select * from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert(nvarchar, @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + ')
else'

else
select @command3 = ''
select @command3 = @command3 + '
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, generation, lineage, colv1, marker)
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', 0, @lineage, @cv, @child_marker
from
inserted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + '
and not exists
(select * from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert(nvarchar, @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + ')
'

end

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '
end '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

-- select @command3 = '
-- delete ' + @current_mappings_viewname + ' from
-- ' + @current_mappings_viewname + ' cpmv join ' + @viewname + ' mc
-- on cpmv.tablenick = mc.tablenick
-- and mc.tablenick = ' + convert(nvarchar(11), @immediate_child_nickname) + '
-- and cpmv.tablenick = ' + convert(nvarchar(11), @immediate_child_nickname) + '
-- and cpmv.rowguid = mc.rowguid
-- and cpmv.publication_number = ' + convert(nvarchar, @publication_number) + '
-- and mc.marker = @child_marker '
-- insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
select @command3 = '
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @publication_number) + ', ' + convert (nvarchar(11), @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', ' + quotename(object_name(@objid)) + '.partition_id
from ( '
+ @partition_inserted_view_rule + ' ) as ' + quotename(object_name(@objid))
+ ' JOIN ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
ON '
+ '(' + @join_filterclause + ')
WHERE not exists
(select * from '
+ @quoted_current_mappings_viewname + ' where publication_number = ' + convert(nvarchar, @publication_number) + '
and tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + '
and partition_id = '
+ quotename(object_name(@objid)) + '.partition_id)
'


insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

if @child_expand_proc is not null and @child_expand_proc != ' '
begin
set @command3 = '
exec dbo.'
+ quotename(@child_expand_proc) + ' @marker = @child_marker, @inherit_pastchanges_generation = -1, @parent_being_updated = 0, @trigger_type = 0 '
insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
end

select @command3 = '
end '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

InsertTriggerGetNextChild:

fetch next from immediate_children into @immediate_child_table_name, @immediate_child_objid, @immediate_child_nickname,
@immediate_child_partition_viewname, @join_filter_id, @join_filterclause, @join_unique_key, @child_expand_proc,
@pubid, @child_has_col_tracking
end

close immediate_children
deallocate immediate_children

end -- end @article_published = 1

select @command4 = '

return
FAILURE:
if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return '

insert into #tmptriggercmd (phase, cmdtext) values (20, @command4)
end

-- upd_ trigger generation
-- Call separate routine to add update trigger --
if @trigger_type = 1
begin
exec @retcode=sys.sp_MSaddupdatetrigger @quoted_source_table_name, @owner, @object, @artid, @column_tracking, @viewname, @trigname, @current_mappings_viewname, @past_mappings_viewname, @genhistory_viewname
if @retcode<>0 or @@ERROR<>0 return (1)
end

-- del_ trigger generation
-- Now make the delete trigger --
-- NOTE: owner name removed

if @trigger_type = 2
begin
select @command=NULL, @command2=NULL, @command3=NULL, @command4=NULL

set @command = '
create trigger '
+ @quoted_trigname + ' on ' + @quoted_db_source_table_name + ' FOR DELETE ' + @notforrepl_str1 + ' AS
declare @is_mergeagent bit, @at_publisher bit, @retcode smallint

set rowcount 0
set transaction isolation level read committed
'


if exists (select * from dbo.sysmergearticles where nickname = @tablenick
and (pubid in (select pubid from dbo.sysmergepublications where use_partition_groups = 1)
or published_in_tran_pub = 1 ))
begin
select @command = @command + '
exec @retcode = sys.sp_MSisreplmergeagent @is_mergeagent output, @at_publisher output
if @@error <> 0 or @retcode <> 0
begin
rollback tran
return
end '

end
else
begin
select @command = @command + '
select @is_mergeagent = convert(bit, sessionproperty('
'replication_agent''))
select @at_publisher = 0 '

end

if (@notforrepl_bit = 1)
select @command = @command + @notforrepl_str2

if @partition_options = 2 or @partition_options = 3
select @command = @command + '
if @is_mergeagent = 1 and @at_publisher = 1
return '


select @command = @command + '
declare @article_rows_deleted int
declare @xe_message varbinary(1000)
select @article_rows_deleted = count(*) from deleted
if @article_rows_deleted=0
return
declare @tablenick int, @replnick binary(6),
@lineage varbinary(311), @newgen bigint, @oldmaxversion int, @child_newgen bigint,
@child_oldmaxversion int, @child_metadatarows_updated int, @cv varbinary(1),
@logical_record_parent_oldmaxversion int, @logical_record_lineage varbinary(311), @logical_record_parent_regular_lineage varbinary(311), @logical_record_parent_gencur bigint,
@num_parent_rows int, @logical_record_parent_rowguid uniqueidentifier, @parent_row_inserted bit, @rowguid uniqueidentifier
declare @dt datetime, @nickbin varbinary(8), @error int
'


if @logical_record_view is not null and @is_top_level_logical_record_parent = 0
select @command = @command + '
declare @logical_record_distinct_parent_rowguids table (logical_record_parent_rowguid uniqueidentifier unique) '

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
set nocount on
select @tablenick = '
+ @tablenickchar + '
if @article_rows_deleted = 1 select @rowguid = rowguidcol from deleted
select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick
select @dt = getdate()

select @replnick = '
+ sys.fn_varbintohexstr(@replnick) + '
set @nickbin= @replnick + 0xFF

select @newgen = NULL
select top 1 @newgen = generation from '
+ @quoted_genhistory_viewname + ' with (rowlock, updlock, readpast)
where art_nick = '
+ @tablenickchar + ' and genstatus = 0
'

if @level_generations_in_trigger = 1
select @command = @command + '
and changecount <= ('
+ convert(nvarchar, @gen_change_threshold) + ' - isnull(@article_rows_deleted,0))'
select @command = @command + '
if @newgen is NULL
begin
insert into '
+ @quoted_genhistory_viewname + ' with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, @tablenick, @nickbin, @dt, @article_rows_deleted)
select @error = @@error, @newgen = @@identity
if @error<>0 or @newgen is NULL
goto FAILURE
end'

if @level_generations_in_trigger = 1
select @command = @command + '
else
begin
-- now update the changecount of the generation we go to reflect the number of rows we put in this generation
update '
+ @quoted_genhistory_viewname + ' with (rowlock)
set changecount = changecount + @article_rows_deleted
where generation = @newgen
if @@error<>0 goto FAILURE
end
'

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

if @logical_record_view is not null
begin
select top 1 @logical_record_parent_nickname = logical_record_parent_nickname
from dbo.sysmergepartitioninfo
where artid = @artid
and logical_record_parent_nickname is not null
end

-- Do not update the tombstone table if the delete_tracking property is set to false on the article.
-- This property when set blocks replication of deletes. Note: It causes non-convergence.
if @delete_tracking = 0
begin
set @command2 = '
set @lineage = { fn UPDATELINEAGE(0x0, @replnick, @oldmaxversion+1) }'

end
else
begin
set @command2 = '
set @lineage = { fn UPDATELINEAGE(0x0, @replnick, @oldmaxversion+1) } '


insert into #tmptriggercmd (phase, cmdtext) values (3, @command2)

if @logical_record_view is null or @is_top_level_logical_record_parent = 0
set @command2 = '
if @article_rows_deleted = 1
insert into '
+ @quoted_tsview + ' with (rowlock) (rowguid, tablenick, type, lineage, generation)
select @rowguid, @tablenick, 1, isnull((select { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) } from
'
+ @quoted_viewname + ' c with (rowlock) where c.tablenick = @tablenick and c.rowguid = @rowguid),@lineage), @newgen
else
insert into '
+ @quoted_tsview + ' with (rowlock) (rowguid, tablenick, type, lineage, generation)
select d.rowguidcol, @tablenick, 1, { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) }, @newgen from
deleted d left outer join '
+ @quoted_viewname + ' c with (rowlock) on c.tablenick = @tablenick and c.rowguid = d.rowguidcol
'

else
set @command2 = '
if @article_rows_deleted = 1
insert into '
+ @quoted_tsview + ' with (rowlock) (rowguid, tablenick, type, lineage, generation, logical_record_parent_rowguid, logical_record_lineage)
select @rowguid, @tablenick, 1, isnull((select { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) } from
'
+ @quoted_viewname + ' c with (rowlock) where c.tablenick = @tablenick and c.rowguid = @rowguid),@lineage), @newgen,
@rowguid, isnull((select { fn UPDATELINEAGE(COALESCE(c.logical_record_lineage, @lineage), @replnick, @oldmaxversion+1) } from
'
+ @quoted_viewname + ' c with (rowlock) where c.tablenick = @tablenick and c.rowguid = @rowguid),@lineage)
else
insert into '
+ @quoted_tsview + ' with (rowlock) (rowguid, tablenick, type, lineage, generation, logical_record_parent_rowguid, logical_record_lineage)
select d.rowguidcol, @tablenick, 1, { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @replnick, @oldmaxversion+1) }, @newgen,
d.rowguidcol, { fn UPDATELINEAGE(COALESCE(c.logical_record_lineage, @lineage), @replnick, @oldmaxversion+1) }
from deleted d left outer join '
+ @quoted_viewname + ' c with (rowlock) on c.tablenick = @tablenick and c.rowguid = d.rowguidcol
'


insert into #tmptriggercmd (phase, cmdtext) values (3, @command2)

set @command2 = '
if @@error <> 0
GOTO FAILURE '

end
insert into #tmptriggercmd (phase, cmdtext) values (3, @command2)

if exists (select * from dbo.sysmergepartitioninfoview
where nickname = @tablenick and partition_view_id is not null)
begin
if @logical_record_view is not null
set @command2 = '
if @article_rows_deleted = 1
delete from '
+ @quoted_current_mappings_viewname + ' with (rowlock)
where tablenick = @tablenick
and rowguid = @rowguid
else'

else
select @command2 = ''
select @command2 = @command2 + '
delete from '
+ @quoted_current_mappings_viewname + ' with (rowlock)
from deleted d
JOIN '
+ @quoted_current_mappings_viewname + ' cpm with (rowlock)
ON cpm.tablenick = @tablenick
and cpm.rowguid = d.'
+ @quoted_rgcol + '
option (force order, loop join) '


insert into #tmptriggercmd (phase, cmdtext) values (3, @command2)

declare partition_view_ids CURSOR LOCAL FAST_FORWARD FOR
select partition_view_id, pubid, partition_deleted_view_rule
from dbo.sysmergepartitioninfoview
where nickname = @tablenick
and partition_view_id is not null -- if partition_view_id is NULL, this publication is not using partition groups
for read only

open partition_view_ids

fetch next from partition_view_ids into @partition_view_id, @pubid, @partition_deleted_view_rule
while (@@fetch_status <> -1)
begin

select top 1 @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid

select @partition_view_name = 'dbo.' + quotename(object_name(@partition_view_id))

if @partition_view_name is not null
begin

-- handle the case when there is a delete cascade constraint. When the following conditions are satisfied, we
-- need to delete the rows from all partitions:
-- (1) the ON DELETE CASCADE constraint is set
-- (2) the rows in the parent table do not exist
-- (yiche)

declare @join_table_name nvarchar(270), -- the parent table name
@join_filter_clause nvarchar(270),
@parent_partition_view_name nvarchar(517) -- the partition view name for the parent table

declare @jt nvarchar(270) -- the unquoted name for the join_table_name
select @jt = join_articlename, @join_filter_clause = join_filterclause
from dbo.sysmergesubsetfilters
where pubid = @pubid and artid = @artid -- note that an article might belongs to multiple publication

select @join_table_name = QUOTENAME(@jt)

select @parent_partition_view_name = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(object_name(partition_view_id))
from dbo.sysmergepartitioninfoview, sys.objects
where object_name(objid) = @jt and object_id = @objid and pubid = @pubid

if ( not exists (select *
from sys.foreign_keys fks JOIN dbo.sysmergearticles art
on fks.parent_object_id = art.objid
where fks.delete_referential_action = 1 -- on delete cascade
and art.nickname = @tablenick )
OR ( @join_filter_clause IS NULL )
) -- endif
begin



select @command2 = '
-- update any ppm row that already exist with this gen
update ppm set ppm.generation = case when @is_mergeagent = 1 then 0 else @newgen end
from '
+ @quoted_past_mappings_viewname + ' ppm with (rowlock) inner join deleted v
on ppm.tablenick =@tablenick and ppm.rowguid = v.'
+ @quoted_rgcol + '
-- insert the past partition mapping into gen 0 if this is the merge agent
insert into '
+ @quoted_past_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id, generation,reason)
select distinct '
+ convert(nvarchar(100), @publication_number) + ', @tablenick, v.' + @quoted_rgcol + ', v.partition_id, case when @is_mergeagent = 1 then 0 else @newgen end, 1
from ( '
+ @partition_deleted_view_rule + ' ) as v
if (@@ROWCOUNT <= 0)
begin
select @xe_message = CAST('
'replica_id: '' + convert(nvarchar(100), @replnick) + '', article_id: '' + convert(nvarchar(100), @tablenick) + '', rowguid: '' + case when @article_rows_deleted = 1 then convert(nvarchar(100), @rowguid) else N''0'' end + '', generation: '' + case when @is_mergeagent = 1 then N''0'' else convert(nvarchar(100), @newgen) end + '', Reason: -1'' AS varbinary(1000));
exec master..sp_repl_generateevent 1, N'
'Event : ppm_insert'', @xe_message
end
'


end
else
begin

select @command2 = '
-- update any ppm row that already exist with this gen
update ppm set ppm.generation = case when @is_mergeagent = 1 then 0 else @newgen end
from '
+ @quoted_past_mappings_viewname + ' ppm with (rowlock) inner join deleted v
on ppm.tablenick =@tablenick and ppm.rowguid = v.'
+ @quoted_rgcol + '
if ( not exists (select *
from deleted '
+ QUOTENAME(@source_table) + ' JOIN ' + @parent_partition_view_name + ' ' + @join_table_name + '
on ( '
+ @join_filter_clause+' ) ))
begin
insert into '
+ @quoted_past_mappings_viewname + ' with (rowlock) ( publication_number, tablenick, rowguid, partition_id, generation, reason)
select distinct '
+ convert( nvarchar(100), @publication_number)+', @tablenick, v.' + @quoted_rgcol +', -1, case when @is_mergeagent = 1 then 0 else @newgen end, 1
from deleted as v
end
else
begin
insert into '
+ @quoted_past_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id, generation,reason)
select distinct '
+ convert(nvarchar(100), @publication_number) + ', @tablenick, v.' + @quoted_rgcol + ', v.partition_id, case when @is_mergeagent = 1 then 0 else @newgen end, 1
from ( '
+ @partition_deleted_view_rule + ' ) as v
end
if (@@ROWCOUNT <= 0)
begin
select @xe_message = CAST('
'replica_id: '' + convert(nvarchar(100), @replnick) + '', article_id: '' + convert(nvarchar(100), @tablenick) + '', rowguid: '' + case when @article_rows_deleted = 1 then convert(nvarchar(100), @rowguid) else N''0'' end + '', generation: '' + case when @is_mergeagent = 1 then N''0'' else convert(nvarchar(100), @newgen) end + '', Reason: -1'' AS varbinary(1000));
exec master..sp_repl_generateevent 1, N'
'Event : ppm_insert'', @xe_message
end
'


end

insert into #tmptriggercmd (phase, cmdtext) values (3, @command2)
end

fetch next from partition_view_ids into @partition_view_id, @pubid, @partition_deleted_view_rule
end
end

if @logical_record_view is not null and @is_top_level_logical_record_parent = 0
begin
select @command = '
declare @logical_record_rowguid_parent_rowguid_mapping table (rowguid uniqueidentifier, logical_record_parent_rowguid uniqueidentifier) '


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

if @is_top_level_logical_record_parent = 0
begin
select @command = '
insert into @logical_record_rowguid_parent_rowguid_mapping
select '
+ @quoted_rgcol + ', logical_record_parent_rowguid from (' + @logical_record_deleted_view_rule + ') as lrdvr
where lrdvr.logical_record_parent_rowguid is not null

insert into @logical_record_distinct_parent_rowguids
select distinct logical_record_parent_rowguid from @logical_record_rowguid_parent_rowguid_mapping

select @num_parent_rows = @@rowcount

if @num_parent_rows = 1
begin
-- this could happen when all rows being inserted are the children of the same parent row.
select @logical_record_parent_rowguid = logical_record_parent_rowguid from @logical_record_distinct_parent_rowguids
exec @retcode = sys.sp_MSupdate_singlelogicalrecordmetadata '
+ convert(nvarchar, @logical_record_parent_nickname) + ', @logical_record_parent_rowguid, @replnick, @parent_row_inserted output
if @@error <> 0 or @retcode <> 0
goto FAILURE
end
else if @num_parent_rows > 1
begin
select top 1 @logical_record_parent_oldmaxversion = maxversion_at_cleanup
from dbo.sysmergearticles
where nickname = '
+ convert(nvarchar,@logical_record_parent_nickname) + '

-- the code below will get an open generation for the parent
select @logical_record_parent_gencur = NULL
select top 1 @logical_record_parent_gencur = generation from '
+ @quoted_genhistory_viewname + ' with (rowlock, updlock, readpast)
where art_nick = '
+ convert(nvarchar,@logical_record_parent_nickname) + '
and genstatus = 0'

if @level_generations_in_trigger = 1
select @command = @command + '
and changecount <= ('
+ convert(nvarchar, @gen_change_threshold) + '-isnull(@num_parent_rows,0))'
select @command = @command + '
if @logical_record_parent_gencur is NULL
begin
insert into '
+ @quoted_genhistory_viewname + ' with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, '
+ convert(nvarchar,@logical_record_parent_nickname) + ', @nickbin, @dt, @num_parent_rows)
select @error = @@error, @logical_record_parent_gencur = @@identity
if @error<>0 or @logical_record_parent_gencur is NULL
goto FAILURE
end'

if @level_generations_in_trigger = 1
select @command = @command + '
else
begin
-- do the update right away to change the changecount to include the rows that we just put in the generation
update '
+ @quoted_genhistory_viewname + ' with (rowlock)
set changecount = changecount + @num_parent_rows
where generation = @logical_record_parent_gencur
if @@error<>0 goto FAILURE
end'

insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

select @command = '
update '
+ @quoted_viewname + ' with (rowlock)
set logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @logical_record_parent_oldmaxversion+1) },
generation = @logical_record_parent_gencur
from @logical_record_distinct_parent_rowguids lrpg join '
+ @quoted_viewname + ' with (rowlock)
on tablenick = '
+ convert(nvarchar, @logical_record_parent_nickname) + '
and rowguid = lrpg.logical_record_parent_rowguid
option (force order, loop join)

delete from @logical_record_distinct_parent_rowguids
where logical_record_parent_rowguid in (select rowguid from '
+ @quoted_viewname + '
where tablenick = '
+ convert(nvarchar, @logical_record_parent_nickname) + ')

-- Now @logical_record_distinct_parent_rowguids is left with parent rowguids that are not present in MSmerge_contents.
if exists (select * from @logical_record_distinct_parent_rowguids)
begin
select @logical_record_lineage = { fn UPDATELINEAGE(0x0, @replnick, @logical_record_parent_oldmaxversion+1) }

-- if no cleanup done yet, use 1 as the version.
if @logical_record_parent_oldmaxversion = 1
select @logical_record_parent_regular_lineage = { fn UPDATELINEAGE(0x0, @replnick, 1) }
else
select @logical_record_parent_regular_lineage = @logical_record_lineage

insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen,
logical_record_parent_rowguid, logical_record_lineage)
select distinct '
+ convert(nvarchar, @logical_record_parent_nickname) + ', lrpg.logical_record_parent_rowguid,
@logical_record_lineage, 0x00, @logical_record_parent_gencur, NULL, lrpg.logical_record_parent_rowguid,
@logical_record_lineage
from @logical_record_distinct_parent_rowguids lrpg
where not exists (select rowguid from '
+ @quoted_viewname + ' with (rowlock) where tablenick = ' + convert(nvarchar, @logical_record_parent_nickname) + '
and rowguid = lrpg.logical_record_parent_rowguid)
end
end '


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)

-- if partition groups functionality is being used, we need to insert the partition ids for these
-- newly inserted contents rows.

declare lrp_partition_view_ids CURSOR LOCAL FAST_FORWARD FOR
select partition_view_id, pubid, objid
from dbo.sysmergepartitioninfoview where nickname = @logical_record_parent_nickname
and partition_view_id is not null
for read only

open lrp_partition_view_ids

fetch next from lrp_partition_view_ids into @lrp_partition_view_id, @lrp_pubid, @lrp_objid

while (@@fetch_status <> -1)
begin

select top 1 @lrp_publication_number = publication_number from dbo.sysmergepublications where pubid = @lrp_pubid
select @lrp_rgcol = quotename(name) from sys.columns where object_id = @lrp_objid and is_rowguidcol = 1
select @lrp_partition_view_name = 'dbo.' + quotename(object_name(@lrp_partition_view_id))
if @lrp_partition_view_name is not null
begin

select @command = NULL
select @command = '
if @num_parent_rows = 1
begin
-- if we did not insert the parent row, we have no business inserting the mapping row.
if @parent_row_inserted = 1
begin
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @lrp_publication_number) + ', '
+ convert(nvarchar,@logical_record_parent_nickname) + ', v.' + @lrp_rgcol + ', v.partition_id
from '
+ @lrp_partition_view_name + ' as v with (rowlock)
where v.'
+ @lrp_rgcol + ' = @logical_record_parent_rowguid
end
end
else
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @lrp_publication_number) + ', '
+ convert(nvarchar,@logical_record_parent_nickname) + ', v.' + @lrp_rgcol + ', v.partition_id
from @logical_record_distinct_parent_rowguids as lrpg, '
+ @lrp_partition_view_name + ' as v with (rowlock)
where v.'
+ @lrp_rgcol + ' = lrpg.logical_record_parent_rowguid
and not exists (select * from '
+ @quoted_current_mappings_viewname + ' mcpg with (rowlock)
where mcpg.tablenick = '
+ convert(nvarchar,@logical_record_parent_nickname) + '
and mcpg.rowguid = v.'
+ @lrp_rgcol + ')
'


insert into #tmptriggercmd (phase, cmdtext) values (1, @command)
end

fetch next from lrp_partition_view_ids into @lrp_partition_view_id, @lrp_pubid, @lrp_objid
end

close lrp_partition_view_ids
deallocate lrp_partition_view_ids
end
end

if @logical_record_view is not null and @is_top_level_logical_record_parent = 0
begin
select @command = '
update ts with (rowlock) set ts.logical_record_parent_rowguid = lrrprm.logical_record_parent_rowguid
from @logical_record_rowguid_parent_rowguid_mapping lrrprm, '
+ @quoted_tsview + ' ts with (rowlock)
where ts.tablenick = @tablenick
and ts.rowguid = lrrprm.rowguid
option (force order, loop join) '


insert into #tmptriggercmd (phase, cmdtext) values (3, @command)
end


-- this expansion code is only needed on the publisher
if @article_published = 1
begin

-- Logical records are based on PK-FK relationships. So when a parent row is deleted, it can be safely
-- assumed that none of the child rows exist (they should have been deleted beforehand).
-- As a result, the following expansion code will not
-- find any children if logical records are in use. So, don't need to worry about determining the
-- logical_record_parent_rowguid for the child rows brought in via expansion below.

declare immediate_children CURSOR LOCAL FAST_FORWARD FOR
select distinct quotename(object_name(sma.objid)), sma.objid, sma.nickname, 'dbo.' + quotename(object_name(sma.partition_view_id)),
ssf.join_filterid, ssf.join_filterclause, ssf.join_unique_key, sma.expand_proc, sma.pubid, sma.column_tracking
from dbo.sysmergesubsetfilters ssf, dbo.sysmergepartitioninfoview sma
where ssf.join_nickname = @tablenick
and sma.nickname = ssf.art_nickname
and sma.pubid = ssf.pubid
and ssf.filter_type = 1
and sma.partition_view_id is not null
for read only

open immediate_children

fetch next from immediate_children into @immediate_child_table_name, @immediate_child_objid, @immediate_child_nickname,
@immediate_child_partition_viewname, @join_filter_id, @join_filterclause, @join_unique_key, @child_expand_proc, @pubid, @child_has_col_tracking

if (@@fetch_status <> -1)
begin
select @command3 = '
declare @child_marker uniqueidentifier
declare @child_rowcount int
set @child_marker = newid() '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
end

while (@@fetch_status <> -1)
begin

exec @retcode = sys.sp_MSis_joinfilter_based_on_PK_UQ_constraints @pubid = @pubid,
@join_filter_id = @join_filter_id,
@dri_based = @child_cannot_pre_exist output,
@unique_constraint_based = @parent_columns_unique output

if @@error <> 0 or @retcode <> 0
return 1

if @child_cannot_pre_exist = 1
goto DeleteTriggerGetNextChild

select @owner_qualified_immediate_child_table_name =
quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(object_name(object_id))
from sys.objects with (nolock) where object_id =
(select top 1 repl_view_id from dbo.sysmergepartitioninfoview
where pubid = @pubid and objid = @immediate_child_objid)

select top 1 @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid

select top 1 @child_join_colname = name from sys.columns where object_id = @immediate_child_objid
and sys.fn_MSisfilteredcolumn(@join_filterclause, name, @immediate_child_objid) = 1

select @child_rgcol = quotename(name) from sys.columns where object_id = @immediate_child_objid and is_rowguidcol = 1

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
select @command3 = '
select @child_rowcount = count(*) from '

+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from
deleted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + ')
'

end
else
begin
select @command3 = '
select @child_rowcount = count(*) from
deleted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')
'

end
insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

-- touch the immediate children of deleted rows
select @command3 = '
if @child_rowcount > 0
begin
select @child_oldmaxversion= maxversion_at_cleanup
from dbo.sysmergearticles
where nickname = '
+ convert(nvarchar, @immediate_child_nickname) + '

-- the code below will get an open generation for the child article
select @child_newgen = NULL
select top 1 @child_newgen = generation from '
+ @quoted_genhistory_viewname + ' with (rowlock, updlock, readpast)
where art_nick = '
+ convert(nvarchar,@immediate_child_nickname) + '
and genstatus = 0'

if @level_generations_in_trigger = 1
select @command3 = @command3 + '
and changecount <= ('
+ convert(nvarchar, @gen_change_threshold) + '- isnull(@child_rowcount,0))'
select @command3 = @command3 + '
if @child_newgen is NULL
begin
insert into '
+ @quoted_genhistory_viewname + ' with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, '
+ convert(nvarchar,@immediate_child_nickname) + ', @nickbin, @dt, @child_rowcount)
select @error=@@error, @child_newgen = @@identity
if @error<>0 or @child_newgen is NULL
goto FAILURE
end'

if @level_generations_in_trigger = 1
select @command3 = @command3 + '
else
begin
-- do the update right away to change the changecount to include the rows that we just put in the generation
update '
+ @quoted_genhistory_viewname + ' with (rowlock)
set changecount = changecount + @child_rowcount
where generation = @child_newgen
if @@error<>0 goto FAILURE
end'



insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
select @command3 = '
update '
+ @quoted_viewname + ' with (rowlock) set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker
from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and mc.rowguid in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from
deleted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + ') '
end
else
begin
select @command3 = '
update '
+ @quoted_viewname + ' with (rowlock) set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker
from
deleted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock), '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and mc.rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + '
and '
+ '(' + @join_filterclause + ') '
end

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '
select @child_metadatarows_updated = @@rowcount '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '
if @child_metadatarows_updated < @child_rowcount '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

set @command3 = '
begin
set @lineage = { fn UPDATELINEAGE(0x0, @replnick, @child_oldmaxversion+1) } '


insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

if @child_has_col_tracking = 1
set @command3 = '
set @cv = 0xFF '

else
set @command3 = '
set @cv = NULL '


set @command3 = @command3 + '
insert into '
+ @quoted_viewname + ' with (rowlock) (tablenick, rowguid, generation, lineage, colv1, marker) '
insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
set @command3 = '
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', @child_newgen, @lineage, @cv, @child_marker
from '

+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + ' from
deleted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + ')
and not exists
(select * from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert(nvarchar, @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + ')
'

end
else
begin
set @command3 = '
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', 0, @lineage, @cv, @child_marker
from
deleted '
+ quotename(object_name(@objid)) + ', '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')' + '
and not exists
(select * from '
+ @quoted_viewname + ' mc with (rowlock)
where mc.tablenick = '
+ convert(nvarchar, @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + ')
'

end

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '
end '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '
delete '
+ @quoted_current_mappings_viewname + ' with (rowlock) from
'
+ @quoted_viewname + ' mc with (rowlock) join ' + @quoted_current_mappings_viewname + ' cpmv with (rowlock)
on cpmv.tablenick = mc.tablenick
and mc.tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and cpmv.tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and cpmv.rowguid = mc.rowguid
and cpmv.publication_number = '
+ convert(nvarchar, @publication_number) + '
and mc.marker = @child_marker '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select @command3 = '
insert into '
+ @quoted_current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
from '
+ @quoted_viewname + ' mc with (rowlock)
JOIN '
+ @immediate_child_partition_viewname + ' v with (rowlock)
ON mc.tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and mc.rowguid = v.'
+ @child_rgcol + '
and mc.marker = @child_marker
'


insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

select top 1 @partition_deleted_view_rule = partition_deleted_view_rule
from dbo.sysmergepartitioninfoview
where pubid = @pubid
and nickname = @tablenick

if @partition_deleted_view_rule is not null and @partition_deleted_view_rule <> ' '
begin

select @command3 = '
insert into '
+ @quoted_past_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id, generation, reason)
select distinct '
+ convert(nvarchar, @publication_number) + ', ' + convert (nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', ' +
quotename(object_name(@objid)) + '.partition_id, @child_newgen, 1
from ( '
+ @partition_deleted_view_rule + ' ) as ' + quotename(object_name(@objid))
+ ', ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ '(' + @join_filterclause + ')
'


insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
end

if @child_expand_proc is not null and @child_expand_proc != ' '
begin
set @command3 = '
exec dbo.'
+ quotename(@child_expand_proc) + ' @marker = @child_marker, @inherit_pastchanges_generation = @child_newgen, @parent_being_updated = 0, @trigger_type = 2 '
insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
end

select @command3 = '
end '

insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

DeleteTriggerGetNextChild:

fetch next from immediate_children into @immediate_child_table_name, @immediate_child_objid, @immediate_child_nickname,
@immediate_child_partition_viewname, @join_filter_id, @join_filterclause, @join_unique_key, @child_expand_proc, @pubid, @child_has_col_tracking
end

close immediate_children
deallocate immediate_children

end -- if @article_published = 1

if @logical_record_view is not null
set @command4 = '
if @article_rows_deleted = 1
delete from '
+ @quoted_viewname + ' with (rowlock) where tablenick = @tablenick and rowguid = @rowguid
else'

else
set @command4 = ''
set @command4 = @command4 + '
delete '
+ @quoted_viewname + ' with (rowlock)
from deleted d, '
+ @quoted_viewname + ' cont with (rowlock)
where cont.tablenick = @tablenick and cont.rowguid = d.rowguidcol
option (force order, loop join)'


set @command4 = @command4+'

if @@error <> 0
GOTO FAILURE'


-- Do not update the tombstone table if the delete_tracking property is set to false on the article.
-- This property when set blocks replication of deletes. Note: It causes non-convergence.
if @delete_tracking = 1
begin
set @command4 = @command4 + '

'

end

set @command4 = @command4+'
return
FAILURE:
if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return '

insert into #tmptriggercmd (phase, cmdtext) values (20, @command4)
insert into #tmptriggercmd (phase, cmdtext) values (2, @inscommand)
end

select cmdtext from #tmptriggercmd where cmdtext is not null order by phase, step
drop table #tmptriggercmd

No comments:

Post a Comment

Total Pageviews