Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddmergetriggers_internal

  No additional text.


Syntax

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 (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 (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
    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 @replnic
 
Last revision SQL2008SP2
See also

  sp_MSaddmergetriggers (Procedure)
sp_MSdrop_rlcore (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash