Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddupdatetrigger

  No additional text.


Syntax

create procedure sys.sp_MSaddupdatetrigger
    @source_table       nvarchar(776),      /* source table name */
    @owner              sysname,            /* Owner name of source table */
    @object             sysname,            /* Object name */
    @artid              uniqueidentifier,   /* Article id */
    @column_tracking    int,
    @viewname           sysname,            /* name of view on MSmerge_contents */
    @trigname           sysname,
    @current_mappings_viewname sysname,
    @past_mappings_viewname sysname,
    @genhistory_viewname sysname

AS
    declare @command1 nvarchar(max)
    declare @command2 nvarchar(max)
    declare @command3 nvarchar(max)
    declare @command4 nvarchar(max)
    declare @command5 nvarchar(max)
    declare @command6 nvarchar(max)
    declare @command7 nvarchar(max)
    declare @command8 nvarchar(max)
    declare @command9 nvarchar(max)

    declare @inscommand nvarchar(2000)
    declare @tablenick int
    declare @replnick binary(6)
    declare @viewcols int
    declare @gstr sysname
    declare @tablenickchar nvarchar(11)
    declare @ccols int
    declare @guidstr nvarchar(32)
    declare @colid smallint

    declare @colname     sysname
    declare @cur_name    sysname
    declare @colchar nvarchar(5)
    declare @piece nvarchar(400)

    declare @retcode int
    declare @ifcol nvarchar(4000)
    declare @partchangecnt int
    declare @joinchangecnt int
    declare @logicalrelationchangecnt int
    declare @partchangecnt2 int
    declare @cvstr1    nvarchar(500)
    declare @cvstr2 nvarchar(500)
    declare @missing_cols varbinary(128)
    declare @mapdownbm varbinary(500)
    declare @mapupbm   varbinary(500)
    declare @timestampbm   varbinary(500)
    declare @timestampbmstr varchar(1000)
    declare @missingcolid int
    declare @maxcolid int
    declare @mapdownbmstr varchar(1000)
    declare @mapupbmstr   varchar(1000)
    declare @objid int
    declare @sync_objid int
    declare @partchbm varbinary(500)
    declare @logicalrelationchbm varbinary(500)
    declare @joinchbm varbinary(500)
    declare @partchstr varchar(1002)
    declare @logicalrelationchstr varchar(1002)
    declare @joinchstr varchar(1002)
    declare @column_hole bit
    declare @notforrepl_bit bit
    declare @notforrepl_str1 nvarchar(400)
    declare @notforrepl_str2 nvarchar(400)
    declare @rgcol nvarchar(270)
    declare @child_rgcol nvarchar(270)
    declare @child_has_col_tracking int
    declare @logical_record_view int
    declare @logical_record_deleted_view_rule nvarchar(max)
    declare @subscriber_of_wellpartitionedarticle bit
    declare @article_name nvarchar(130)
    declare @partition_options tinyint
    declare @str_objid nvarchar(15)
    declare @is_republisher_of_article bit
    -- prepare quoted table name
    declare @quoted_source_table_name nvarchar(400)
    declare @dbname nvarchar(130)
    declare @UnqualName    sysname  --rightmost name node
    declare @QualName1     sysname
    declare @is_top_level_logical_record_parent bit
    declare @logical_record_parent_nickname int
    declare @max_colv_size_in_bytes int
    declare @article_published bit
    declare @gen_change_threshold int
    declare @level_generations_in_trigger bit
    declare @child_cannot_pre_exist bit
    declare @parent_columns_unique bit
    declare @join_filter_id int

    select @parent_columns_unique = 0

    select @UnqualName = PARSENAME(@source_table, 1)
    select @QualName1 = PARSENAME(@source_table, 2)
    if @UnqualName IS NULL
         return 1

    if @QualName1 is NULL
         select @QualName1 = schema_name(schema_id) from sys.objects where name=@UnqualName

    set @dbname=PARSENAME(@source_table, 3)
    if @dbname IS NULL
        select @dbname = DB_NAME()
    set @quoted_source_table_name=QUOTENAME(@dbname)+ '.' +QUOTENAME(@QualName1)+ '.' +QUOTENAME(@UnqualName)

    set @notforrepl_bit = 1
    set @ifcol = ''
    set @column_hole = 0
    set @subscriber_of_wellpartitionedarticle = 0

    exec @retcode = sys.sp_MSissubscriber_of_wellpartitionedarticle @artid, @subscriber_of_wellpartitionedarticle output
    if @@error <> 0 or @retcode <> 0
        return 1

    set @objid = OBJECT_ID(@source_table)
    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

    -- Find the bitmap of timestamp columns. The trigger needs to exclude them from
    -- the result of column_updated(). The reason therefore is that we want the colv
    -- to not show changes for those columns, in order to prevent a false conflict.
    -- As the @bm is also used for rowtracked, e.g., for partition changes, we also
    -- do this there, so that the @bm looks the same both times.
    if exists (select * from dbo.sysmergearticles where objid=@objid)
    begin
        exec @retcode= sys.sp_MSset_timestamp_bm
                            @timestampbm= @timestampbm output,
                            @objid= @objid

        if @@error <> 0 or @retcode <> 0 return 1
    end
    else
    begin
        set @timestampbm= 0x00
    end

    exec sys.xp_varbintohexstr @timestampbm, @timestampbmstr output

    select top 1 @sync_objid = sync_objid, @missing_cols = missing_cols,
            @article_name = sma.name,
            @partition_options = partition_options
            from dbo.sysmergearticles sma, dbo.sysmergepartitioninfo smaw
            where sma.artid= @artid
            and sma.objid=@objid
            and sma.artid = smaw.artid
            and sma.pubid = smaw.pubid


    select top 1 @logical_record_view = logical_record_view,
            @logical_record_deleted_view_rule = logical_record_deleted_view_rule
            from dbo.sysmergearticles sma, dbo.sysmergepartitioninfo smaw
            where sma.artid= @artid
            and sma.objid=@objid
            and sma.artid = smaw.artid
            and sma.pubid = smaw.pubid
            and smaw.logical_record_parent_nickname is not null

    select @ccols = count(*) from sys.columns where object_id = @objid and is_computed <> 1 and system_type_id <> type_id('timestamp')
    /* Figure out if there are any holes in the colid sequence */
    select @maxcolid = max(column_id) from sys.columns where object_id = @objid
    if @ccols <> @maxcolid
        select @column_hole = 1

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

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

    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

    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

    set @tablenickchar = convert(nchar, @tablenick)
    set @joinchbm = 0x0
    set @partchbm = 0x0
    set @logicalrelationchbm = 0x0

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

    -- Check if the update trigger 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

    /* Don't let them update the rowguid column */
    set @ifcol = '
    if update(' + QUOTENAME(@rgcol) +    ')
    begin
        if @@trancount > 0
            rollback tran

        RAISERROR (20062, 16, -1)
    end '

    declare col_cursor CURSOR LOCAL FAST_FORWARD for select name, column_id from sys.columns where
		object_id = @objid  and is_computed <> 1 and system_type_id <> type_id('timestamp') order by column_id
	FOR READ ONLY
	
	/* Try to set the ifcol pieces of the trigger */
	open col_cursor
	fetch next from col_cursor into @colname, @colid
	while (@@fetch_status <> -1)
	begin
	
		/* does updating this column change membership in a partial replica? */
		select @partchangecnt = count(*) from dbo.sysmergearticles
			where nickname = @tablenick and
					sys.fn_MSisfilteredcolumn(subset_filterclause, @colname, @objid) = 1
	
		select @partchangecnt2 = count(*) from dbo.sysmergesubsetfilters
			where art_nickname = @tablenick and
					sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @objid) = 1 and
					(filter_type & 1) = 1
	
		select @joinchangecnt = count(*) from dbo.sysmergesubsetfilters
				where join_nickname = @tablenick and
					sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @objid) = 1 and
					(filter_type & 1) = 1
	
		select @logicalrelationchangecnt = count(*) from dbo.sysmergesubsetfilters
				where (join_nickname = @tablenick or art_nickname = @tablenick)
				and sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @objid) = 1
				and (filter_type & 2) = 2
	
		if @partchangecnt > 0 or @partchangecnt2 > 0
			exec sys.sp_MSsetbit @partchbm out, @colid
		if @joinchangecnt > 0
			exec sys.sp_MSsetbit @joinchbm out, @colid
		if @logicalrelationchangecnt > 0
			exec sys.sp_MSsetbit @logicalrelationchbm out, @colid
		/* Repeat the loop with next column */
		fetch next from col_cursor into @colname, @colid
	end
	close col_cursor
	deallocate col_cursor

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

	/* Make strings to initialize variables for partchange, joinchange bitmaps */
	exec sys.xp_varbintohexstr @partchbm, @partchstr out
	exec sys.xp_varbintohexstr @joinchbm, @joinchstr out
	exec sys.xp_varbintohexstr @logicalrelationchbm, @logicalrelationchstr out


    select @mapdownbm =0x00
    select @mapupbm = 0x00
    /*
    ** To see if there is a need for map down.
    */
    if @column_hole<>0
    begin
        set @missingcolid = 1
        while (@missingcolid <= @maxcolid)
        begin
            if not exists (select * from sys.columns where column_id = @missingcolid and
                        object_id = OBJECT_ID(@source_table) and is_computed <> 1 and system_type_id <> type_id('timestamp'))

                    exec sys.sp_MSsetbit @mapdownbm out, @missingcolid
            set @missingcolid = @missingcolid + 1
        end
    end
    set @mapupbm = @missing_cols -- do this at both sides, good for republishing.

    exec sys.xp_varbintohexstr @mapdownbm, @mapdownbmstr out
    exec sys.xp_varbintohexstr @mapupbm, @mapupbmstr out

    if @column_tracking <> 0
        begin
        /* Set cv pieces appropriately */
        set @cvstr1 = '
            set @lineage = { fn UPDATELINEAGE(0x0, @replnick, @oldmaxversion+1) }
            set @cv = 0xFF
            if (@@error <> 0)
                begin
                goto FAILURE
                end
            set @cv = { fn UPDATECOLVBM(@cv, @replnick, @bm, @missingbm, { fn GETMAXVERSION(@lineage) }) }
        '
        set @cvstr2 = '
                colv1 = { fn UPDATECOLVBM(colv1, @replnick, @bm, @missingbm, { fn GETMAXVERSION({ fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) }) }) } '
        end
    else
        begin
        set @cvstr1 = '   set @lineage = { fn UPDATELINEAGE(0x0, @replnick, @oldmaxversion+1) }
            set @cv = NULL
     '
        set @cvstr2 = ' colv1 = NULL '
        end
    /* UNDONE maybe remove null guid checks in SQL SERVER 7.0 */
    select @command1 = '
create trigger ' + @trigname + ' on ' + @quoted_source_table_name + ' FOR UPDATE ' + @notforrepl_str1 + ' AS
    declare @is_mergeagent bit, @at_publisher bit, @retcode int

    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 @command1 = @command1 + '
        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 @command1 = @command1 + '
        select @is_mergeagent = convert(bit, sessionproperty(''replication_agent''))
        select @at_publisher = 0 '
    end

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

    if @partition_options = 2 or @partition_options = 3
        select @command1 = @command1 + '
        if @is_mergeagent = 1 and @at_publisher = 1
            return '
    insert into #tmptriggercmd(phase, cmdtext) values (1, @command1)

    select @command1 = '
    declare @article_rows_updated int
    -- Should use @@rowcount below but there is a bug because of which sometimes in the presence of
    -- other triggers on the table, the @@rowcount cannot be relied on.
    select @article_rows_updated = count(*) from inserted

    if @article_rows_updated=0
        return
    declare @contents_rows_updated int, @updateerror int, @rowguid uniqueidentifier
    , @bm varbinary(500), @missingbm varbinary(500), @lineage varbinary(311), @cv varbinary(' + convert(nvarchar(13), @max_colv_size_in_bytes) + '), @partchangebm varbinary(500), @joinchangebm varbinary(500), @logicalrelationchangebm varbinary(500)
    , @tablenick int, @partchange int, @joinchange int, @logicalrelationchange int, @oldmaxversion int
    , @partgen bigint, @newgen bigint, @child_newgen bigint, @child_oldmaxversion int, @child_metadatarows_updated int
    , @logical_record_parent_oldmaxversion int, @logical_record_lineage varbinary(311), @logical_record_parent_regular_lineage varbinary(311), @logical_record_parent_gencur bigint, @logical_record_parent_rowguid uniqueidentifier
    , @replnick binary(6), @num_parent_rows int, @parent_row_inserted bit
    declare @dt datetime
    declare @nickbin varbinary(8)
    declare @error int
    declare @null_lineage_updated bit'

    if @logical_record_view is not null and (@is_top_level_logical_record_parent = 0 or @article_published = 1)
        select @command1 = @command1 + '
    declare @logical_record_distinct_parent_rowguids table (logical_record_parent_rowguid uniqueidentifier, unique(logical_record_parent_rowguid))
    declare @logical_record_distinct_parent_rowguids_copy table (logical_record_parent_rowguid uniqueidentifier, unique(logical_record_parent_rowguid))
    declare @logical_record_distinct_parents_count int'

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


        select @command1 = '
    set nocount on

    set @tablenick = ' + @tablenickchar + '

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

    select @null_lineage_updated = 0

    select @oldmaxversion = maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick
    select @dt = getdate()

    -- Use intrinsic funtion to set bits for updated columns
    set @bm = columns_updated()'
    insert into #tmptriggercmd(phase, cmdtext) values (1, @command1)

    select @command1 = '
    select @newgen = NULL
    select top 1 @newgen = generation from ' + @genhistory_viewname + ' with (rowlock, updlock, readpast)
        where art_nick = ' + @tablenickchar + ' and
            genstatus = 0 '
    if @level_generations_in_trigger = 1
        select @command1 = @command1  + ' and
            changecount <= (' + convert(nvarchar, @gen_change_threshold) + ' - isnull(@article_rows_updated,0))'
    select @command1 = @command1  + '
    if @newgen is NULL
    begin
        insert into ' + @genhistory_viewname + ' with (rowlock)
        (guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
             values   (newid(), 0, @tablenick, @nickbin, @dt, @article_rows_updated)
        select @error = @@error, @newgen = @@identity
        if @error<>0 or @newgen is NULL
            goto FAILURE
    end'
    if @level_generations_in_trigger = 1
        select @command1 = @command1  + '
    else
    begin
        -- now update the changecount of the generation we go to reflect the number of rows we put in this generation
        update ' + @genhistory_viewname + ' with (rowlock)
            set changecount = changecount + @article_rows_updated
            where generation = @newgen
        if @@error<>0 goto FAILURE
    end'

    if @timestampbm <> 0x00
    begin
        select @command1 = @command1 + '
        -- Remove timestamp columns from columns_updated(), so that colv does not show a false conflict.
        exec @retcode= sys.sp_firstonly_bitmap
                        @inputbitmap1= @bm,
                        @inputbitmap2= ' + @timestampbmstr + ',
                        @resultbitmap3= @bm output

        if @@error<>0 or @retcode<>0 goto FAILURE'
    end

    select @command1 = @command1 + '

    /* save a copy of @bm */
    declare @origin_bm varbinary(500)
    set  @origin_bm =  @bm

    /* only do the map down when needed */
    set @missingbm = ' + @mapupbmstr

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

    -- Checking for updating a row with lineage = 0x00 only needs to be done if
    -- this article is partitioned and a child article.  This is due to the fact
    -- that 0x00 will only be inserted by the insertion of a parent row that
    -- must insert contents entries for it's children.
    select @command2 = ''
    if exists (select partition_view_id from dbo.sysmergepartitioninfo where artid = @artid and
        pubid in (select pubid from dbo.sysmergesubsetfilters where artid = @artid)
        and partition_view_id is not null)
    begin
    	select @command2 = '
    	/* See if we will be updating contents rows that have 0x00 lineage.	 If so, we will need to insert cpmv entries */
    	if exists (select * from inserted as I JOIN ' + @viewname + ' as V with (rowlock)
    		ON (I.rowguidcol=V.rowguid)
    		and V.tablenick = @tablenick
    		and V.lineage = 0x00)
    	begin
    		select @null_lineage_updated = 1
    	end
       '
    end
    select @command2 = @command2 + '
	/* See if the partition might have changed */ ' + case when @partchbm = 0x0 then '
		set @partchange = 0 '
	else '
		set @partchange= { fn INTERSECTBITMAPS (@bm, @partchangebm) } '
	end + '
	/* See if a column used in a join filter changed */ ' + case when @joinchbm = 0x0 then '
		set @joinchange = 0 '
	else '
		set @joinchange= { fn INTERSECTBITMAPS (@bm, @joinchangebm) } '
	end + '
	/* See if a column used in a logical record relationship changed */ ' + case when @logicalrelationchbm = 0x0 then '
		set @logicalrelationchange = 0 '
	else '
		set @logicalrelationchange= { fn INTERSECTBITMAPS (@bm, @logicalrelationchangebm) }
		if @logicalrelationchange = 1 and @is_mergeagent = 0
		begin
			raiserror(22530, 16, -1, ''' + @article_name + ''')
			if @@trancount > 0
				rollback tran
			return
		end '
	end

	
    if @mapdownbm<>0x00
        select @command2 = @command2 + '
    execute sp_mapdown_bitmap ' + @mapdownbmstr +', @bm output '

    if @subscriber_of_wellpartitionedarticle = 1
    begin
        select @command2 = @command2 + '
    if (@joinchange = 1 or @partchange = 1) and (@is_mergeagent = 0 or (select trigger_nestlevel()) <> 1)
    begin
        raiserror(21583, 16, -1, ''' + @article_name + ''')
        if @@trancount > 0
            rollback tran
        return
    end '
    end
    else
      -- if partition_options is 1, we do not allow DML on that subscriber that will cause partition changes
      -- yiche,  bug 405196
        if @partition_options = 1 and @article_published = 0
        begin
        select @command2 = @command2 + '
    if (@joinchange = 1 or @partchange = 1) and (@is_mergeagent = 0 or (select trigger_nestlevel()) <> 1)
    begin
    raiserror(20733, 16, -1, ''' + @article_name + ''', ' + convert(char(3), @partition_options) + ')
    if @@trancount > 0
        rollback tran
        return
    end '
        end

    select @command2 = @command2 + '
    ' + @cvstr1 + '
    if @joinchange = 1 or @partchange = 1
        set @partgen = @newgen
'
    insert into #tmptriggercmd(phase, cmdtext) values (8, @command2)

    select @command3 = '
    else
        set @partgen = NULL'

    if @logical_record_view is not null
    begin
        if @is_top_level_logical_record_parent = 0
        begin
            set @command3 = @command3 + '
    if @article_rows_updated = 1
    begin
        select @rowguid = rowguidcol from inserted

        update ' + @viewname + ' with (rowlock)
        set lineage = { fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) },
            generation = @newgen,
            partchangegen = case when (@partchange = 1 or @joinchange = 1) then @newgen else partchangegen end,
            ' + @cvstr2 + '
        where tablenick = @tablenick
        and rowguid = @rowguid

        select @updateerror = @@error, @contents_rows_updated = @@rowcount
        ' + case when @inscommand is null or @inscommand = ' ' then ' ' else ' if @joinchange = 1 or @partchange = 1 ' + @inscommand end + '
        if @article_rows_updated <> @contents_rows_updated
        begin '

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

            select @command3= '
            insert into ' + @viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid)
            select @tablenick, @rowguid, @lineage, @cv, @newgen, @partgen, logical_record_parent_rowguid
            from ' + object_name(@logical_record_view) + ' lrv
            where lrv.' + @rgcol + ' = @rowguid
            and not exists (select rowguid from ' + @viewname + ' with (rowlock) where tablenick = @tablenick and rowguid = @rowguid) '

            select @command3 = @command3 + '
                if @@error <> 0
                    GOTO FAILURE
        end
    end
    else
    begin'
        end
        else    -- is_top_level_logical_record_parent = 1
        begin
        set @command3 = @command3 + '
    if @article_rows_updated = 1
    begin
        select @rowguid = rowguidcol from inserted

        update ' + @viewname + ' with (rowlock)
        set lineage = { fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) },
            logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @oldmaxversion+1) },
            logical_record_parent_rowguid = @rowguid,
            generation = @newgen,
            partchangegen = case when (@partchange = 1 or @joinchange = 1) then @newgen else partchangegen end,
            ' + @cvstr2 + '
        where tablenick = @tablenick
        and rowguid = @rowguid

        select @updateerror = @@error, @contents_rows_updated = @@rowcount
        ' + case when @inscommand is null or @inscommand = ' ' then ' ' else ' if @joinchange = 1 or @partchange = 1 ' + @inscommand end + '
        if @article_rows_updated <> @contents_rows_updated
        begin '

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

            select @command3= '
            insert into ' + @viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid, logical_record_lineage)
            select @tablenick, @rowguid, @lineage, @cv, @newgen, @partgen, @rowguid, @lineage
            where not exists (select rowguid from ' + @viewname + ' with (rowlock) where tablenick = @tablenick and rowguid = @rowguid) '

            select @command3 = @command3 + '
                if @@error <> 0
                    GOTO FAILURE
        end
    end
    else
    begin'
        end
    end

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

    if @is_top_level_logical_record_parent = 0
        select @command3 = '
        update ' + @viewname + ' with (rowlock)
        set lineage = { fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) },
            generation = @newgen,
            partchangegen = case when (@partchange = 1 or @joinchange = 1) then @newgen else partchangegen end,
            ' + @cvstr2 + '
        FROM inserted as I JOIN ' + @viewname + ' as V with (rowlock)
        ON (I.rowguidcol=V.rowguid)
        and V.tablenick = @tablenick
        option (force order, loop join)

        select @updateerror = @@error, @contents_rows_updated = @@rowcount
        ' + case when @inscommand is null or @inscommand = ' ' then ' ' else ' if @joinchange = 1 or @partchange = 1 ' + @inscommand end + '
        if @article_rows_updated <> @contents_rows_updated
        begin '
    else
        select @command3 = '
        update ' + @viewname + ' with (rowlock)
        set lineage = { fn UPDATELINEAGE(lineage, @replnick, @oldmaxversion+1) },
            generation = @newgen,
            logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @oldmaxversion+1) },
            logical_record_parent_rowguid = I.rowguidcol,
            partchangegen = case when (@partchange = 1 or @joinchange = 1) then @newgen else partchangegen end,
            ' + @cvstr2 + '
        FROM inserted as I JOIN ' + @viewname + ' as V with (rowlock)
        ON (I.rowguidcol=V.rowguid)
        and V.tablenick = @tablenick
        option (force order, loop join)

        select @updateerror = @@error, @contents_rows_updated = @@rowcount
        ' + case when @inscommand is null or @inscommand = ' ' then ' ' else ' if @joinchange = 1 or @partchange = 1 ' + @inscommand end + '
        if @article_rows_updated <> @contents_rows_updated
        begin '

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

    if @logical_record_view is null
        select @command3= '
            insert into ' + @viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen)
            select @tablenick, rowguidcol, @lineage, @cv, @newgen, @partgen
            from inserted i
            where not exists (select rowguid from ' + @viewname + ' with (readcommitted, rowlock, readpast) where tablenick = @tablenick and rowguid = i.rowguidcol) '
    else if @is_top_level_logical_record_parent = 0
        select @command3= '
            insert into ' + @viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid)
            select @tablenick, i.rowguidcol, @lineage, @cv, @newgen, @partgen, logical_record_parent_rowguid
            from inserted i, ' + object_name(@logical_record_view) + ' lrv with (rowlock)
            where i.rowguidcol = lrv.' + @rgcol + '
            and not exists (select rowguid from ' + @viewname + ' with (readcommitted, rowlock, readpast) where tablenick = @tablenick and rowguid = i.rowguidcol)
            '
    else -- @is_top_level_logical_record_parent = 1
        select @command3= '
            insert into ' + @viewname + ' with (rowlock) (tablenick, rowguid, lineage, colv1, generation, partchangegen, logical_record_parent_rowguid, logical_record_lineage)
            select @tablenick, i.rowguidcol, @lineage, @cv, @newgen, @partgen, i.rowguidcol, @lineage
            from inserted i
            where not exists (select rowguid from ' + @viewname + ' with (readcommitted, rowlock, readpast) where tablenick = @tablenick and rowguid = i.rowguidcol) '

    select @command3 = @command3 + '
            if @@error <> 0
                GOTO FAILURE
        end'

    if @logical_record_view is not null
        select @command3 = @command3 + '
    end'

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

    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 @is_top_level_logical_record_parent = 0
        begin

            select @command3 = '

    if @article_rows_updated = 1
    begin
        select @logical_record_parent_rowguid = logical_record_parent_rowguid
        from ' + object_name(@logical_record_view) + ' lrv
        where lrv.' + @rgcol + ' = @rowguid

        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
        insert into @logical_record_distinct_parent_rowguids values (@logical_record_parent_rowguid)
        insert into @logical_record_distinct_parent_rowguids_copy values (@logical_record_parent_rowguid)
    end '
            insert into #tmptriggercmd (phase, cmdtext) values (9, @command3)
                select @command3 = '
    else
    begin

        insert into @logical_record_distinct_parent_rowguids
        select distinct logical_record_parent_rowguid
        from inserted i, '
        + object_name(@logical_record_view) + ' lrv with (rowlock)
        where i.rowguidcol = lrv.' + @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
            insert into @logical_record_distinct_parent_rowguids_copy values (@logical_record_parent_rowguid)
        end  '
            insert into #tmptriggercmd (phase, cmdtext) values (9, @command3)
            select @command3 = '
        else if @num_parent_rows > 1
        begin
            insert into @logical_record_distinct_parent_rowguids_copy
            select * from @logical_record_distinct_parent_rowguids
            select @logical_record_distinct_parents_count = count(*) from @logical_record_distinct_parent_rowguids

            if @@rowcount > 0
            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 ' + @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 @command3 = @command3 + '
                        and changecount <= (' + convert(nvarchar, @gen_change_threshold) + '-isnull(@logical_record_distinct_parents_count,0))'
            select @command3 = @command3 + '
                if @logical_record_parent_gencur is NULL
                begin
                    insert into ' + @genhistory_viewname + ' with (rowlock)
                        (guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
                    values (newid(), 0, ' + convert(nvarchar,@logical_record_parent_nickname) + ', @nickbin, @dt, @logical_record_distinct_parents_count)
                    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 @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 ' + @genhistory_viewname + ' with (rowlock)
                    set changecount = changecount + @logical_record_distinct_parents_count
                    where generation = @logical_record_parent_gencur
                    if @@error<>0 goto FAILURE
                end'

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

            select @command3 = '

                update ' + @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 ' + @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 ' + @viewname + ' with (rowlock)
                    where tablenick = ' + convert(nvarchar, @logical_record_parent_nickname) + ') '

            insert into #tmptriggercmd (phase, cmdtext) values (9, @command3)
            select @command3 = '
                -- 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 ' + @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 ' + @viewname + ' with (rowlock) where tablenick = ' + convert(nvarchar, @logical_record_parent_nickname) + '
                                        and rowguid = lrpg.logical_record_parent_rowguid)
                end
            end
        end
end '

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

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

            declare @lrp_partition_view_id int, @lrp_partition_view_name nvarchar(270), @lrp_pubid uniqueidentifier,
                    @lrp_publication_number smallint, @lrp_objid int, @lrp_rgcol nvarchar(270)

            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 @command3 = NULL
                    select @command3 = '
    if (@num_parent_rows = 1 or @article_rows_updated = 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 ' + @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 ' + @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 ' + @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 (9, @command3)
                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 is_top_level_logical_parent = 0
        else if @is_top_level_logical_record_parent = 1 and @article_published = 1
        begin
            select @command3 = '

    if @article_rows_updated = 1
    begin
        select @logical_record_parent_rowguid = @rowguid
        insert into @logical_record_distinct_parent_rowguids values (@logical_record_parent_rowguid)
        insert into @logical_record_distinct_parent_rowguids_copy values (@logical_record_parent_rowguid)
    end '
        insert into #tmptriggercmd (phase, cmdtext) values (9, @command3)
            select @command3 = '
    else
    begin

        insert into @logical_record_distinct_parent_rowguids
        select distinct logical_record_parent_rowguid from inserted i, '
        + object_name(@logical_record_view) + ' lrv with (rowlock)
        where i.rowguidcol = lrv.' + @rgcol + '
        and lrv.logical_record_parent_rowguid is not null

        insert into @logical_record_distinct_parent_rowguids_copy
            select * from @logical_record_distinct_parent_rowguids
    end '
            insert into #tmptriggercmd (phase, cmdtext) values (9, @command3)
        end --is_top_level_logical_parent = 1
    end -- logical_record_view is not null

    if @article_published = 1
    begin
        declare @partition_view_id int, @partition_view_name nvarchar(270), @pubid uniqueidentifier, @partition_deleted_view_rule nvarchar(max),
                @partition_inserted_view_rule nvarchar(max), @publication_number smallint, @basetable_ownerqualified_replviewname nvarchar(130)

        select @command4 = NULL, @command5 = NULL

        declare partition_view_ids CURSOR LOCAL FAST_FORWARD FOR
        select partition_view_id, pubid, partition_deleted_view_rule, 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_deleted_view_rule, @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 @partition_view_name = 'dbo.' + quotename(object_name(@partition_view_id))

            select @basetable_ownerqualified_replviewname =
                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 = @objid)

            if @partition_view_name is not null
            begin

                if @command4 is null
                    select @command4 = '

    if (@partchange = 1 or @joinchange = 1)
    begin '
                insert into #tmptriggercmd(phase, cmdtext) values (10, @command4)

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

		--obtain the parent table name
		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)

		--obtain the partition view name for the parent table
		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
		
		-- Now we compose the update trigger code dependending on the following cases:
		--	(1) The general case when there is on ON DELETE/UPDATE clause for the PK-FK. All we need to do
		--         is to move the rows to the past partition mappings
		--	(2) The case when we cannot determin the past partition of the deleted rows. This includes
		--		(a) The case when we have SET NULL. In this case we have no way to tell what
		--		      is the past partition the deleted rows belong to, so we need to delete them from all partitions
		--		(b) the case when we cannot find the parent table linked by the PK-FK
		--	(3) The rest cases, when we can tell either to delete from all partition or not, by detecting the
		--        join of the deleted rows with the parent view
		if ( not exists (select *
				from sys.foreign_keys fks JOIN dbo.sysmergearticles art
				on fks.parent_object_id = art.objid
				where  -- the delete trigger of the parent table might fire the update trigger of the child table
					(
						ObjectProperty(fks.object_id, 'CnstIsDeleteCascade') = 1 -- on cascade
						or fks.delete_referential_action = 2  -- on delete set null
					 	or fks.delete_referential_action = 3  -- on delete set default
						  -- for the case of update set null, set default, we won't be able to find the parent rows either
						  or ObjectProperty(fks.object_id, 'CnstIsUpdateCascade')=1 -- on cascade
						 or fks.update_referential_action =2 -- on update set null
						 or fks.update_referential_action=3  -- on update set default
					)
					and art.nickname = @tablenick )
			OR (@join_filter_clause IS NULL)
		) -- case (1)
		
		begin

                select @command4 = '
        insert into ' + @past_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id, generation, reason)
        select distinct ' + convert(nvarchar, @publication_number) + ', @tablenick, v.' + @rgcol + ', v.partition_id, @newgen, 0
        from ( ' + @partition_deleted_view_rule + ' ) as v '

        	end -- end of case (1)
        	else  if( exists (select * from sys.foreign_keys fks JOIN dbo.sysmergearticles art
 					on fks.parent_object_id = art.objid
 					where ( fks.delete_referential_action = 2
 						or fks.delete_referential_action = 3
 						-- we cannot determine the current past partition mappings
 						-- for all ON UPDATE cases, so we need to delete the child
 						-- rows from all partitions
						or ObjectProperty(fks.object_id, 'CnstIsUpdateCascade')=1
 						or fks.update_referential_action = 2
 						or fks.update_referential_action = 3)
 					)
 					-- in case we cannot find the source table name and the parent partition view name
 					-- we need to delete the rows from all partitions. This might happen when we have
 					-- multiple publications
				or @parent_partition_view_name is null
				or @join_table_name is null
 			)-- case (2)
	      		-- now we handle the case when we know we should delete the rows from all partitions
        		-- In the case of SET NULL we should always remove the child rows from all subscribers
			-- because in these cases we have no way to tell the past partition
			-- mappings the deleted/updated rows belong to.
 			begin
 			select @command4='
				--Since we might not find the parent rows by the PK-FK, we should delete them from all partitions
				insert into ' + @past_mappings_viewname + ' with (rowlock) ( publication_number, tablenick, rowguid, partition_id, generation, reason)
				select distinct ' + convert( nvarchar, @publication_number)+', @tablenick, v.' + @rgcol +', -1, case when @is_mergeagent = 1 then 0 else @newgen end, 1
				from deleted as v
 			'
 			end --end of case (2)
 		else
        	begin    -- case (3)



			select @command4 = '
			if ( not exists (select *
					from deleted ' + QUOTENAME(@UnqualName)  + '  JOIN  ' + @parent_partition_view_name + '  ' + @join_table_name + '
					  on ( '+ @join_filter_clause+' ) ))
					begin
						insert into ' + @past_mappings_viewname + ' with (rowlock) ( publication_number, tablenick, rowguid, partition_id, generation, reason)
						select distinct ' + convert( nvarchar, @publication_number)+', @tablenick, v.' + @rgcol +', -1, case when @is_mergeagent = 1 then 0 else @newgen end, 1
						from deleted as v
					end
					else
					begin
					        insert into ' + @past_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id, generation,reason)
					        select distinct ' + convert(nvarchar, @publication_number) + ', @tablenick, v.' + @rgcol + ', v.partition_id, case when @is_mergeagent = 1 then 0 else @newgen end, 1
					        from ( ' + @partition_deleted_view_rule + ' ) as v 				
					end
				'
        	end --end of case (3)


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

                select @command6 = '
    if (@partchange = 1 or @joinchange = 1)
    begin'
                if @logical_record_view is not null
                    select @command6 = @command6 + '
        if @article_rows_updated = 1
            insert into ' + @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.' + @rgcol + ' = @rowguid
        else'
                select @command6 = @command6 + '
            insert into ' + @current_mappings_viewname + ' (publication_number, tablenick, rowguid, partition_id)
            select distinct ' + convert(nvarchar, @publication_number) + ', @tablenick, v.' + @rgcol + ', v.partition_id
            from ( ' + @partition_inserted_view_rule + ' ) as v
    end '
                if @partition_options = 2 or @partition_options = 3
                    select @command6 = @command6 + '
    else if (@contents_rows_updated <> @article_rows_updated and (@is_mergeagent = 0 or @at_publisher = 0)) '
                else

                select @command6 = @command6 + '
    else if (@contents_rows_updated <> @article_rows_updated OR @null_lineage_updated = 1)'

                select @command6 = @command6 + '
    begin'
                if @logical_record_view is not null
                    select @command6 = @command6 + '
        if @article_rows_updated = 1
            insert into ' + @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.' + @rgcol + ' = @rowguid
                and not exists (select * from ' + @current_mappings_viewname + ' with (readcommitted, rowlock, readpast) where
                publication_number = ' + convert(nvarchar, @publication_number) + ' and tablenick = @tablenick and rowguid = @rowguid and partition_id = v.partition_id)
        else'
                select @command6 = @command6 + '
            insert into ' + @current_mappings_viewname + ' with (rowlock) (publication_number, tablenick, rowguid, partition_id)
            select distinct ' + convert(nvarchar, @publication_number) + ', @tablenick, v.' + @rgcol + ', v.partition_id
            from ( ' + @partition_inserted_view_rule + ' ) as v
            where not exists (select * from ' + @current_mappings_viewname + ' with (readcommitted, rowlock, readpast) where
            publication_number = ' + convert(nvarchar, @publication_number) + ' and tablenick = @tablenick and rowguid = v.' + @rgcol + ' and partition_id = v.partition_id)
    end '
                insert into #tmptriggercmd(phase, cmdtext) values (12, @command6)
            end
            fetch next from partition_view_ids into @partition_view_id, @pubid, @partition_deleted_view_rule, @partition_inserted_view_rule, @partition_options
        end

    end -- @article_published = 1

    if @command4 is not null
    begin
        if @logical_record_view is not null
            select @command5 = '
        if @article_rows_updated = 1
            delete from ' + @current_mappings_viewname + '
            from ' + @current_mappings_viewname + ' cpm with (rowlock)
            where cpm.tablenick = @tablenick
            AND cpm.rowguid = @rowguid
        else'
        else
            select @command5 = ''
        select @command5 = @command5 + '
            delete from ' + @current_mappings_viewname + '
            from deleted d
            JOIN ' + @current_mappings_viewname + ' cpm with (rowlock)
            ON cpm.tablenick = @tablenick
            AND cpm.rowguid = d.' + @rgcol + '
            option (force order, loop join)'
        insert into #tmptriggercmd(phase, cmdtext) values (11, @command5)
    end

    if @command5 is not null
    begin
        select @command5 = '
    end '
        insert into #tmptriggercmd(phase, cmdtext) values (11, @command5)
    end

    if @article_published = 1
    begin
        declare @immediate_child_table_name nvarchar(130), @owner_qualified_immediate_child_table_name nvarchar(270),
                @immediate_child_objid int, @immediate_child_nickname int, @immediate_child_partition_viewname nvarchar(130),
                @join_filterclause nvarchar(1000), @child_join_colname nvarchar(130), @join_unique_key int, @child_expand_proc sysname

        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 sma.partition_view_id is not null
        and (ssf.filter_type & 1) = 1
        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 = '
    if @partchange = 1 or @joinchange = 1
    begin
        declare @child_marker uniqueidentifier
        declare @inserted_child_rowcount int, @deleted_child_rowcount int
        set @child_marker = newid()
        '
            insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)
            select @command3 = '
    end    '
            insert into #tmptriggercmd (phase, cmdtext) values(29, @command3)
        end

        while (@@fetch_status <> -1)
        begin
            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

            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

        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_updated = 1
            select @inserted_child_rowcount = count(*) from '
            + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + '
            where ' + @child_rgcol + ' in
                (select ' + @immediate_child_table_name + '.' + @child_rgcol + ' from '
                + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name
                + ', ' + @basetable_ownerqualified_replviewname + ' '
                + quotename(object_name(@objid)) + ' where '
                + quotename(object_name(@objid)) + '.' + @rgcol + ' = @rowguid and '
                + '(' + @join_filterclause + ')' + ')
        else'
                else
                    select @command3 = ''
                select @command3 = @command3 + '
            select @inserted_child_rowcount = count(*) from ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + '
            where ' + @child_rgcol + ' in
                (select ' + @immediate_child_table_name + '.' + @child_rgcol + ' from ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name
                + ', inserted ' + quotename(object_name(@objid)) + ' where '
                + '(' + @join_filterclause + ')' + ')'

                if @logical_record_view is null
                    select @command3 = @command3 + '
        if @joinchange = 1
            select @deleted_child_rowcount = count(*) from ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + '
            where ' + @child_rgcol + ' in
                (select ' + @immediate_child_table_name + '.' + @child_rgcol + ' from ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ', deleted ' + quotename(object_name(@objid)) + '
                where ' + '(' + @join_filterclause + ')' + ')
        else
            select @deleted_child_rowcount = 0 '
            end
            else
            begin
                if @logical_record_view is not null
                    select @command3 = '
        if @article_rows_updated = 1
            select @inserted_child_rowcount = count(*) from ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name
            + ', ' + @basetable_ownerqualified_replviewname + ' '
            + quotename(object_name(@objid)) + ' where '
            + quotename(object_name(@objid)) + '.' + @rgcol + ' = @rowguid and '
            + '(' + @join_filterclause + ')
        else'
                else
                    select @command3 = ''
                select @command3 = @command3 + '
            select @inserted_child_rowcount = count(*) from ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name
            + ', inserted '
            + quotename(object_name(@objid)) + ' where '
            + '(' + @join_filterclause + ')'

                if @logical_record_view is null
                    select @command3 = @command3 + '
        if @joinchange = 1
            select @deleted_child_rowcount = count(*) from ' + @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ', deleted ' + quotename(object_name(@objid)) + '
            where ' + '(' + @join_filterclause + ')' + '
        else
 select @deleted_child_rowcount = 0 '
            end
            insert into #tmptriggercmd (phase, cmdtext) values(14, @command3)

            -- touch the immediate children of deleted rows
            select @command3 = '
        if @inserted_child_rowcount > 0 or @deleted_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 ' + @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(@inserted_child_rowcount,0) - isnull(@deleted_child_rowcount,0))'

            select @command3 = @command3 + '
            if @child_newgen is NULL
            begin
                insert into ' + @genhistory_viewname + ' with (rowlock)
                (guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
                      values  (newid(), 0, ' + convert(nvarchar,@immediate_child_nickname) + ', @nickbin, @dt, 1)
                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 + '
            -- do the update right away to change the changecount to include the rows that we just put in the generation
            update ' + @genhistory_viewname + ' with (rowlock)
            set changecount = changecount + isnull(@inserted_child_rowcount,0) + isnull(@deleted_child_rowcount,0)
            where generation = @child_newgen
            if @@error<>0 goto FAILURE
'


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

            select @command3 = '
            if @inserted_child_rowcount > 0
            begin '

            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
                    select @command3 = '
            if @article_rows_updated = 1
                    update ' + @viewname + ' with (rowlock) set generation = 0, marker = @child_marker,
                    lineage = { fn UPDATELINEAGE(linea
 
Last revision SQL2008SP2
See also

  sp_MSaddmergetriggers_internal (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