Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreatelightweighttriggers

  No additional text.


Syntax
create procedure sys.sp_MScreatelightweighttriggers
	@artid		uniqueidentifier
as
	set nocount on

	declare @tablenick			int
	declare @destination_object	sysname
	declare @guidstring			nchar(32)
	declare @oldtriggername		sysname
	declare @cmd				nvarchar(max)
	declare @qualified_name 	nvarchar(517)
	declare @retcode 			int
	declare @destination_owner	sysname
	declare @rowguidcol			sysname
	declare @objid				int
	declare @coltracked			bit
	declare @enumtype_str		nvarchar(100) -- Used for delete trigger
	declare @delete_tracking		bit

	select top 1
			@tablenick= nickname,
			@coltracked= column_tracking,
			@destination_object= destination_object,
			@destination_owner= destination_owner,
			@objid= objid,
			@delete_tracking=delete_tracking
		from dbo.sysmergearticles where artid = @artid

	if @destination_owner is NULL or @destination_owner=''
		select @qualified_name = quotename(@destination_object)
	else
		select @qualified_name = quotename(@destination_owner) + '.' + quotename(@destination_object)

    exec @retcode= sys.sp_MSguidtostr @artid, @guidstring out
    if @@error <> 0 or @retcode <> 0 return 1

	-- remove heavyweight triggers, if there are any
	set @oldtriggername= 'MSmerge_ins_' + @guidstring
	set @cmd= 'if object_id(''' + @oldtriggername + ''', ''TR'') is not null
					drop trigger ' + quotename(@oldtriggername)
	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1

	set @oldtriggername= 'MSmerge_upd_' + @guidstring
	set @cmd= 'if object_id(''' + @oldtriggername + ''', ''TR'') is not null
					drop trigger ' + quotename(@oldtriggername)
	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1

	set @oldtriggername= 'MSmerge_del_' + @guidstring
	set @cmd= 'if object_id(''' + @oldtriggername + ''', ''TR'') is not null
					drop trigger ' + quotename(@oldtriggername)
	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1
	
	-- remove lightweight triggers, if they are already here for some reason
	set @oldtriggername= 'MSmerge_ins_lws_' + @guidstring
	set @cmd= 'if object_id(''' + @oldtriggername + ''', ''TR'') is not null
					drop trigger ' + quotename(@oldtriggername)
	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1

	set @oldtriggername= 'MSmerge_upd_lws_' + @guidstring
	set @cmd= 'if object_id(''' + @oldtriggername + ''', ''TR'') is not null
					drop trigger ' + quotename(@oldtriggername)
	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1

	set @oldtriggername= 'MSmerge_del_lws_' + @guidstring
	set @cmd= 'if object_id(''' + @oldtriggername + ''', ''TR'') is not null
					drop trigger ' + quotename(@oldtriggername)
	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1

	-- remove download-only trigger, if it exists
	set @oldtriggername= 'MSmerge_downloadonly_' + @guidstring
	set @cmd= 'if object_id(''' + @oldtriggername + ''', ''TR'') is not null
					drop trigger ' + quotename(@oldtriggername)
	exec @retcode= sys.sp_executesql @cmd
	if @@error <> 0 or @retcode <> 0 return 1

    -- Determine the name of the rowguidcol.
    select @rowguidcol= name from sys.columns
    	where object_id = @objid and
    		  is_rowguidcol = 1

	-- Create the insert trigger
	exec @retcode= sys.sp_MScreatelightweightinsertorupdatetriggers
							@guidstring= @guidstring,
							@qualified_name= @qualified_name,
							@rowguidcol= @rowguidcol,
							@tablenick= @tablenick,
							@inserttrigger= 1

	if @@error <> 0 or @retcode <> 0 return 1
						
	-- Create the update trigger
	exec @retcode= sys.sp_MScreatelightweightinsertorupdatetriggers
							@guidstring= @guidstring,
							@qualified_name= @qualified_name,
							@rowguidcol= @rowguidcol,
							@tablenick= @tablenick,
							@inserttrigger= 0

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

	if 1=@coltracked
	begin
		set @enumtype_str= '@COLUMNS_ENUMERATED_ChangedOnly'
	end
	else
	begin
		set @enumtype_str= '@COLUMNS_ENUMERATED_AllOnOtherReason'
	end

	-- Build up the command string for the delete trigger
	set @cmd=
'create trigger ' + quotename('MSmerge_del_lws_' + @guidstring) + ' on ' + @qualified_name + ' after delete
as
	set nocount on

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

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

	declare @metarows_count int
	declare @tablenick int
	declare @err int
	declare @METADATA_TYPE_DeleteLightweight tinyint'

	if 1=@coltracked
	begin
		set @cmd= @cmd + '
	declare @COLUMNS_ENUMERATED_ChangedOnly tinyint

	set @COLUMNS_ENUMERATED_ChangedOnly= 0'
	end
	else
	begin
			set @cmd= @cmd + '
	declare @COLUMNS_ENUMERATED_AllOnOtherReason tinyint

	set @COLUMNS_ENUMERATED_AllOnOtherReason= 2'
	end

	set @cmd= @cmd + '
	set @METADATA_TYPE_DeleteLightweight= 10

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

	-- If delete_tracking is true update the entry in MSmerge_rowtrack
	-- if it is false then delete the entry from MSmerge_rowtrack.
	if @delete_tracking=1
	begin
		set @cmd= @cmd + '
	update rt
		set rt.changetype= @METADATA_TYPE_DeleteLightweight,
			rt.changedcolumns= null,
			rt.sync_cookie= null,
			rt.columns_enumeration= ' + @enumtype_str + ',
			rt.changed= sys.fn_MSdayasnumber(getdate())
		from dbo.MSmerge_rowtrack as rt join deleted as d
		on rt.rowguid = d.rowguidcol and rt.tablenick = @tablenick

	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 @baserows_count > @metarows_count
	begin
		insert into dbo.MSmerge_rowtrack
		(
			tablenick,
			rowguid,
			changetype,
			changed,
			changedcolumns,
			columns_enumeration
		)
		select
			@tablenick,
			rowguidcol,
			@METADATA_TYPE_DeleteLightweight,
			sys.fn_MSdayasnumber(getdate()),
			null,
			' + @enumtype_str + '
		from deleted d
		where not exists (select rowguid from dbo.MSmerge_rowtrack
							where tablenick = @tablenick and rowguid = d.rowguidcol)
	end

	return

Failure:
	if @@trancount > 0
		rollback tran
	return
'
	end
	else
	begin
		set @cmd= @cmd + '
	delete rt
		from dbo.MSmerge_rowtrack as rt join deleted as d
		on rt.rowguid = d.rowguidcol and rt.tablenick = @tablenick

	select @err= @@error, @metarows_count= @@rowcount

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

	return

Failure:
	if @@trancount > 0
		rollback tran
	return
'
	end	
		-- create delete trigger
		exec @retcode= sys.sp_executesql @cmd
		if @@error <> 0 or @retcode <> 0 return 1

	return 0

 
Last revision 2008RTM
See also

  sp_MSaddmergetriggers (Procedure)
sp_MScreatelightweightprocstriggersconstraints (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