Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreatelightweightinsertorupdatetriggers

  No additional text.


Syntax
create procedure sys.sp_MScreatelightweightinsertorupdatetriggers
	@guidstring			nchar(32),
	@qualified_name 	nvarchar(517),
	@rowguidcol			sysname,
	@tablenick			int,
	@inserttrigger		bit	-- 1=insert trigger, 0=update trigger
as
	set nocount on

	declare @cmd		nvarchar(max)
	declare @retcode	int
	declare @missingbm	varbinary(128)
	declare @colsupdatedstr varbinary(200)
	declare @column_tracking bit
	declare @missingbmstr varchar(1000)
	declare @filtering_column_id int	
	declare @prev_filtering_column_id int
	declare @filtering_column_name sysname
    declare @timestampbm varbinary(500)
    declare @timestampbmstr varchar(1000)
    declare @objid int

    set @timestampbm= 0x00
    set @objid= object_id(@qualified_name)

    if @objid is null return 1
	
	delete from dbo.MSmerge_filteringcolumns_lightweight
		where tablenick = @tablenick
		and column_id not in (select column_id from sys.columns
							where object_id = @objid)
	
	select @column_tracking= column_tracking,
		   @missingbm= missing_cols
		from dbo.sysmergearticles where objid = @objid
	if @column_tracking is null return 1

	if @missingbm is not null
	begin
		exec sys.xp_varbintohexstr
							@missingbm,
							@missingbmstr out
	end

	set @cmd= 'create trigger '

	if 1=@inserttrigger
	begin
		set @cmd= @cmd + quotename('MSmerge_ins_lws_' + @guidstring) + ' on ' + @qualified_name + ' after insert'
	end
	else
	begin
		set @cmd= @cmd + quotename('MSmerge_upd_lws_' + @guidstring) + ' on ' + @qualified_name + ' after update'

    	-- Find the bitmap of timestamp columns. The trigger needs to exclude them from
    	-- the result of column_updated(), so that MSmerge_rowtrack.changedcolumns
    	-- will not produce a false conflict.
        if exists (select * from dbo.sysmergearticles where objid=@objid and column_tracking=1)
    	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
	end

	set @cmd= @cmd + '
	as
	set nocount on

	if sessionproperty(''replication_agent'') = 1 and (select trigger_nestlevel()) = 1
		return

    declare @retcode int
	declare @baserows_count int
	select @baserows_count = count(*) from inserted
	if @baserows_count=0 return

	'

	if 0=@inserttrigger
	begin
		set @cmd = @cmd + '
		if update(' + quotename(@rowguidcol) + ')
		begin
			if @@trancount > 0 rollback tran
			raiserror (20062, 16, -1)
		end
		'
		
		select top 1 @filtering_column_id = fc.column_id
		from dbo.MSmerge_filteringcolumns_lightweight fc join dbo.sysmergearticles sma
		on fc.tablenick = sma.nickname
		and fc.publication_id = sma.pubid
		and fc.tablenick = @tablenick
		and sma.well_partitioned_lightweight = 1
		order by fc.column_id
			
		while @filtering_column_id is not null
		begin
			select @filtering_column_name = name from sys.columns
			where object_id = @objid
			and column_id = @filtering_column_id
			
			if @filtering_column_name is not null
			begin
				set @cmd = @cmd + '
				if update(' + quotename(@filtering_column_name) + ')
				begin
					if @@trancount > 0 rollback tran
					raiserror(21583, 16, -1, ''' + @qualified_name + ''')
				end
				'
			end
			
			select @prev_filtering_column_id = @filtering_column_id
			select @filtering_column_id = NULL
			
			select top 1 @filtering_column_id = fc.column_id
			from dbo.MSmerge_filteringcolumns_lightweight fc join dbo.sysmergearticles sma
			on fc.tablenick = sma.nickname
			and fc.publication_id = sma.pubid
			and fc.tablenick = @tablenick
			and sma.well_partitioned_lightweight = 1
			and fc.column_id > @prev_filtering_column_id
			order by fc.column_id
		end
	end

	set @cmd= @cmd + '
	declare @metarows_count int
	declare @tablenick int
	declare @err int

	declare @METADATA_TYPE_InsertLightweight tinyint
	declare @COLUMNS_ENUMERATED_AllOnOtherReason tinyint

	set @METADATA_TYPE_InsertLightweight= 7
	set @COLUMNS_ENUMERATED_AllOnOtherReason= 2
	'

	if 0=@inserttrigger
	begin
		set @cmd= @cmd + '
		declare @METADATA_TYPE_UpdateLightweight tinyint
		set @METADATA_TYPE_UpdateLightweight= 8
		'

		if 1=@column_tracking
		begin
			set @cmd= @cmd + '
			declare @COLUMNS_ENUMERATED_ChangedOnly tinyint
			set @COLUMNS_ENUMERATED_ChangedOnly= 0
			'
		end
	end

	set @cmd= @cmd + '
	set @tablenick= ' + cast(@tablenick as nvarchar(20)) + '

	'

	if 1=@inserttrigger
	begin
		set @cmd= @cmd + '

		update rt
			set rt.changetype= @METADATA_TYPE_InsertLightweight,
				rt.changedcolumns= null,
				rt.columns_enumeration= @COLUMNS_ENUMERATED_AllOnOtherReason,
				rt.sync_cookie= null,
				rt.changed= sys.fn_MSdayasnumber(getdate())
			from dbo.MSmerge_rowtrack as rt join inserted as i
			on rt.rowguid = i.rowguidcol and rt.tablenick = @tablenick
	'
	end
	else
	begin
		if 1=@column_tracking
		begin
            set @cmd= @cmd + '
                declare @bm varbinary(128)
                set @bm= columns_updated()'

            if @timestampbm <> 0x00
            begin
                select @cmd = @cmd + '
                -- 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

			if @missingbm is null
			begin
				set @cmd= @cmd + '

				update rt
					set rt.changetype=
							case
								when rt.changetype=@METADATA_TYPE_InsertLightweight and rt.sync_cookie is null then @METADATA_TYPE_InsertLightweight
								else @METADATA_TYPE_UpdateLightweight
							end,
						rt.changedcolumns= sys.fn_MSorbitmaps(isnull(rt.changedcolumns, 0x00), @bm),
						rt.sync_cookie= null,
						rt.changed= sys.fn_MSdayasnumber(getdate())
					from dbo.MSmerge_rowtrack as rt join inserted as i
					on rt.rowguid = i.rowguidcol and rt.tablenick = @tablenick
				'
			end
			else
			begin
				set @cmd= @cmd + '
				exec sp_mapdown_bitmap ' + @missingbmstr + ', @bm output

				update rt
					set rt.changetype=
							case
								when rt.changetype=@METADATA_TYPE_InsertLightweight and rt.sync_cookie is null then @METADATA_TYPE_InsertLightweight
								else @METADATA_TYPE_UpdateLightweight
							end,
						rt.changedcolumns= sys.fn_MSorbitmaps(isnull(rt.changedcolumns, 0x00), @bm),
						rt.sync_cookie= null,
						rt.changed= sys.fn_MSdayasnumber(getdate())
					from dbo.MSmerge_rowtrack as rt join inserted as i
					on rt.rowguid = i.rowguidcol and rt.tablenick = @tablenick
				'
			end
		end
		else
		begin
			set @cmd= @cmd + '

			update rt
				set rt.changetype=
						case
							when rt.changetype=@METADATA_TYPE_InsertLightweight and rt.sync_cookie is null then @METADATA_TYPE_InsertLightweight
							else @METADATA_TYPE_UpdateLightweight
						end,
					rt.changedcolumns= null,
					rt.sync_cookie= null,
					rt.changed= sys.fn_MSdayasnumber(getdate())
				from dbo.MSmerge_rowtrack as rt join inserted as i
				on rt.rowguid = i.rowguidcol and rt.tablenick = @tablenick
			'
		end
	end

	set @cmd= @cmd + '
	select @err= @@error, @metarows_count= @@rowcount

	if @err <> 0
	begin
		raiserror (20041, 16, -1)
		goto Failure
	end

	-- If there were base data rows without corresponding metadata, then @baserows_count > @metarows_count.
	-- In that case, we need to insert rows into MSmerge_rowtrack'

	if 1=@inserttrigger
	begin
		set @cmd= @cmd + '
		if @baserows_count > @metarows_count
		begin
			insert into dbo.MSmerge_rowtrack
			(
				tablenick,
				rowguid,
				rowvector,
				changedcolumns,
				columns_enumeration,
				changetype,
				changed
			)
			select
				@tablenick,
				rowguidcol,
				null,
				null,
				@COLUMNS_ENUMERATED_AllOnOtherReason,
				@METADATA_TYPE_InsertLightweight,
				sys.fn_MSdayasnumber(getdate())
			from inserted i
			where not exists (select rowguid from dbo.MSmerge_rowtrack
								where tablenick = @tablenick and rowguid = i.rowguidcol)
		end'
	end -- 1=@inserttrigger
	else
	begin
		if 1=@column_tracking
		begin
			if @missingbm is null
			begin
				set @cmd= @cmd + '
				if @baserows_count > @metarows_count
				begin
					insert into dbo.MSmerge_rowtrack
					(
						tablenick,
						rowguid,
						changetype,
						changedcolumns,
						columns_enumeration,
						changed
					)
					select
						@tablenick,
						rowguidcol,
						@METADATA_TYPE_UpdateLightweight,
						@bm,
						@COLUMNS_ENUMERATED_ChangedOnly,
						sys.fn_MSdayasnumber(getdate())
					from inserted i
					where not exists (select rowguid from dbo.MSmerge_rowtrack
											where tablenick = @tablenick and rowguid = i.rowguidcol)
				end'
			end
			else
			begin
				set @cmd= @cmd + '
				if @baserows_count > @metarows_count
				begin
					insert into dbo.MSmerge_rowtrack
					(
						tablenick,
						rowguid,
						changetype,
						changedcolumns,
						columns_enumeration,
						changed
					)
					select
						@tablenick,
						rowguidcol,
						@METADATA_TYPE_UpdateLightweight,
						@bm,
						@COLUMNS_ENUMERATED_ChangedOnly,
						sys.fn_MSdayasnumber(getdate())
					from inserted i
					where not exists (select rowguid from dbo.MSmerge_rowtrack
											where tablenick = @tablenick and rowguid = i.rowguidcol)
				end'
			end
		end -- 1=@column_tracking
		else
		begin
			set @cmd= @cmd + '
			if @baserows_count > @metarows_count
			begin
				insert into dbo.MSmerge_rowtrack
				(
					tablenick,
					rowguid,
					changetype,
					changedcolumns,
					columns_enumeration,
					changed
				)
				select
					@tablenick,
					rowguidcol,
					@METADATA_TYPE_UpdateLightweight,
					null,
					@COLUMNS_ENUMERATED_AllOnOtherReason,
					sys.fn_MSdayasnumber(getdate())
				from inserted i
				where not exists (select rowguid from dbo.MSmerge_rowtrack
										where tablenick = @tablenick and rowguid = i.rowguidcol)
			end'
		end
	end

	set @cmd= @cmd + '
	
		return

Failure:
	if @@trancount > 0
		rollback tran
	return
	'

	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1

	return 0

 
Last revision 2008RTM
See also

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