May 7, 2012

sp_MSaddmergetriggers (Transact-SQL MetaData) Definition

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

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

Goto Definition or MetaData

Definition:

sys.sp_MSaddmergetriggers(nvarchar @source_table
, nvarchar @table_owner
, int @column_tracking
, bit @recreate_repl_views)

MetaData:

   


create procedure sys.sp_MSaddmergetriggers
@source_table nvarchar(517), -- was type varchar(92), table name --
@table_owner sysname = NULL,
@column_tracking int = NULL, -- Is column tracking on - default is FALSE --
@recreate_repl_views bit = 1
AS
begin
declare @trigger_type tinyint
declare @cmd nvarchar(4000)
declare @dbname sysname
declare @trigname sysname
declare @instrigname sysname
declare @updtrigname sysname
declare @deltrigname sysname
declare @tablenick int
declare @replnick binary(6)
declare @artid uniqueidentifier
declare @guidstr nvarchar(32)
declare @owner sysname
declare @site sysname
declare @db sysname
declare @object sysname
declare @tablenickchar nvarchar(11)
declare @retcode int
declare @objid int
declare @bitmap varbinary(40)
declare @viewname sysname
declare @tsview sysname
declare @current_mappings_viewname nvarchar(130)
declare @past_mappings_viewname nvarchar(130)
declare @owner_is_admin bit
declare @UnqualName sysname -- rightmost name node
declare @QualName1 sysname
declare @create_upto_triggertype tinyint
declare @source_table_passed_in nvarchar(517)
declare @maintainsmetadata bit
declare @instrigid int
declare @updtrigid int
declare @deltrigid int
declare @pubid uniqueidentifier
declare @prev_pubid uniqueidentifier
declare @before_name nvarchar(130)
declare @beforeview sysname
declare @islightweight bit
declare @genhistory_viewname sysname
declare @article_published bit
declare @subscriber_of_wellpartitionedarticle bit
declare @using_logical_records bit
declare @has_missing_or_exluded_cols bit
declare @use_subscriber_simple_trigger_template bit
declare @identity_support bit
declare @max_colv_size_in_bytes int
declare @max_colv_size_in_bytes_str nvarchar(10)
declare @rgcol sysname
declare @delete_tracking bit
declare @str_objid nvarchar(15)
declare @create_generic_subscriber_trigger bit
declare @sync_type int

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

select @source_table_passed_in = @source_table -- the code below mucks around with @source_table. I need to save off the passed-in value since I pass that to sp_MSaddmergetriggers_internal.

select @dbname = db_name()

if @table_owner is not NULL
select @source_table=QUOTENAME(@table_owner) + '.' + QUOTENAME(@source_table)

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

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

-- Check whether the table is a heavy- or lightweight article.
select top 1 @islightweight=lightweight, @artid=artid from dbo.sysmergearticles where objid=@objid

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

if exists (select * from dbo.sysmergepartitioninfo where artid = @artid and logical_record_view is not null)
select @using_logical_records = 1
else
select @using_logical_records = 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

if exists (select * from sys.columns where object_id = @objid and (is_computed = 1 or system_type_id = type_id('timestamp')))
or exists (select * from dbo.sysmergearticles where artid = @artid and (missing_cols <> 0 or excluded_cols <> 0))
select @has_missing_or_exluded_cols = 1
else
select @has_missing_or_exluded_cols = 0

if exists (select * from dbo.sysmergepartitioninfoview where artid = @artid and identity_support=1)
select @identity_support = 1
else
select @identity_support = 0

if exists (select * from dbo.sysmergepartitioninfoview where artid = @artid and delete_tracking=1)
select @delete_tracking = 1
else
select @delete_tracking = 0

-- we should not use the template to build the triggers when @partition_options of the article is 1
-- yiche, bug 405196
declare @partition_options tinyint
select top 1 @partition_options = partition_options
from dbo.sysmergepartitioninfoview
where artid = @artid

if @article_published = 0 and @using_logical_records = 0 and @subscriber_of_wellpartitionedarticle = 0 and @partition_options <>1
select @use_subscriber_simple_trigger_template = 1
else
select @use_subscriber_simple_trigger_template = 0

if exists (select * from dbo.sysmergearticles where artid = @artid 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 @max_colv_size_in_bytes_str = convert(nvarchar(10), @max_colv_size_in_bytes)
end

begin tran
save tran sp_MSaddmergetriggers

if 0=@islightweight
begin
-- set up the before image table if one is desired
-- exec sys.sp_MScreatebeforetable @objid
select @UnqualName = PARSENAME(@source_table, 1)
select @QualName1 = PARSENAME(@source_table, 2)
if @UnqualName IS NULL
goto FAILURE

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

-- fixup for variable length differences. remove when vars expanded
-- to new SQL SERVER 7.0 lengths

select @owner = @QualName1
select @object = @UnqualName

-- this is to find out if table owner is the dbo. Only then can the object be
-- marked as system object.
if @owner = 'dbo'
select @owner_is_admin = 1
else
select @owner_is_admin = 0

execute @retcode=sys.sp_MStablenickname @owner, @object, @tablenick output
if @retcode<>0 or @@ERROR<>0 goto FAILURE

-- Find out whether this article is updateable at that node.
set @maintainsmetadata= sys.fn_MSarticle_allows_DML_at_this_replica(@artid, default)

-- 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(nvarchar, @tablenick)
exec @retcode=sys.sp_MSguidtostr @artid, @guidstr out
if @retcode<>0 or @@ERROR<>0
goto FAILURE

-- Drop the article's replication triggers if they preexist --
exec sys.sp_MSdroparticletriggers @object, @owner
if @@ERROR <> 0 or @retcode<>0
goto FAILURE

-- owner name removed
set @viewname = 'MSmerge_ctsv_' + @guidstr
set @tsview = 'MSmerge_tsvw_' + @guidstr
set @current_mappings_viewname = 'MSmerge_cpmv_' + @guidstr
set @past_mappings_viewname = 'MSmerge_ppmv_' + @guidstr

set @instrigname = 'MSmerge_ins_' + @guidstr
set @updtrigname = 'MSmerge_upd_' + @guidstr
set @deltrigname = 'MSmerge_del_' + @guidstr

set @genhistory_viewname = 'MSmerge_genvw_' + @guidstr

if object_id(@viewname, 'V') is not NULL
begin
set @cmd = 'drop view dbo.' + @viewname
execute (@cmd)
end

if object_id(@tsview, 'V') is not NULL
begin
set @cmd = 'drop view dbo.' + @tsview
execute (@cmd)
end

if object_id(@current_mappings_viewname, 'V') is not NULL
begin
set @cmd = 'drop view dbo.' + @current_mappings_viewname
execute (@cmd)
end

if object_id(@past_mappings_viewname, 'V') is not NULL
begin
set @cmd = 'drop view dbo.' + @past_mappings_viewname
execute (@cmd)
end

if object_id(@genhistory_viewname, 'V') is not NULL
begin
set @cmd = 'drop view dbo.' + @genhistory_viewname
execute (@cmd)
end

if 1 = @maintainsmetadata
begin
if @recreate_repl_views = 1
begin
-- regenerate repl_view_ that uses triggers for this article @artid
-- they are expected inside trigger generation code
select top 1 @pubid = pubid from dbo.sysmergearticles
where artid = @artid
order by pubid

while @pubid is not null
begin
exec @retcode = sys.sp_MScreate_article_repl_view @pubid, @artid
if @@error <> 0 or @retcode <> 0 goto FAILURE

select @prev_pubid = @pubid
select @pubid = NULL

select top 1 @pubid = pubid from dbo.sysmergearticles
where artid = @artid
and pubid > @prev_pubid
order by pubid
end
end

-- create the before view so that it is consistent with the before image table
-- create the real security before view only after the trigger creation
-- check if there is a before image table. If so create a view from which public can select
select @before_name = OBJECT_NAME(before_image_objid) from dbo.sysmergearticles where objid = @objid
set @beforeview = 'MSmerge_bivw_' + @guidstr
if (@before_name is not null)
begin
-- Create the a view on the before image table if it doesn't already exist. --
-- this view is needed for security purposes since we don't want to grant rights
on the before image table to all users --
if object_id(@beforeview, 'V') is not NULL
begin
exec (' drop view dbo.' + @beforeview)
if @@ERROR<>0 goto FAILURE
end
if object_id(@beforeview, 'V') is NULL
begin
set @cmd = 'create view dbo.' + @beforeview + ' as
select * from dbo.'
+ @before_name
execute (@cmd)
if @@ERROR<>0 goto FAILURE
execute sp_MS_marksystemobject @beforeview
if @@ERROR<>0 goto FAILURE
end
end

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

-- regenerate triggers
select @trigger_type = 0 -- insert trigger (0=insert, 1=update, 2=delete)

select @create_upto_triggertype = 2

-- for nosync subscribers do not use the templated update trigger because we may have deleted columns that need to be considered.
if exists (select * FROM dbo.sysmergesubscriptions
WHERE UPPER(subscriber_server) collate database_default = UPPER(@@servername) collate database_default
and db_name = @dbname and sync_type = 2 and pubid in (select pubid from sysmergearticles where artid = @artid))
begin
select @sync_type = 2
end
else
begin
select @sync_type = 0
end

while (@trigger_type <= @create_upto_triggertype)
begin
select @trigname = case
when @trigger_type = 0 then @instrigname
when @trigger_type = 1 then @updtrigname
when @trigger_type = 2 then @deltrigname
end

if @use_subscriber_simple_trigger_template = 1 and (@trigger_type <> 1 or (@has_missing_or_exluded_cols = 0 and @sync_type <> 2) or @column_tracking = 0)
and (@trigger_type <> 0 or @identity_support = 0) and (@trigger_type <> 2 or @delete_tracking = 1)
begin
select @cmd = 'exec sys.sp_MSaddmergetriggers_from_template ''' + @tablenickchar +
''', N''' + sys.fn_replreplacesinglequote(@source_table_passed_in) collate database_default +
''', ' + sys.fn_replreplacesinglequoteplusprotectstring(@table_owner) collate database_default +
', ' + sys.fn_replreplacesinglequoteplusprotectstring(@rgcol) collate database_default +
', ' + isnull(convert(nvarchar, @column_tracking), 'NULL') + ', ' + convert(nvarchar, @trigger_type) +
', ''' + sys.fn_replreplacesinglequote(@viewname) collate database_default + '''' +
', ''' + sys.fn_replreplacesinglequote(@tsview) collate database_default + '''' +
', ''' + sys.fn_replreplacesinglequote(@trigname) collate database_default + ''''+
', ''' + sys.fn_replreplacesinglequote(@genhistory_viewname) collate database_default + '''' +
', ' + sys.fn_varbintohexstr(@replnick) +
', ''' + @max_colv_size_in_bytes_str + ''''

exec (@cmd)
if @@ERROR<>0 goto FAILURE
end
else
begin

select @cmd = 'exec sys.sp_MSaddmergetriggers_internal N''' + sys.fn_replreplacesinglequote(@UnqualName) collate database_default +
''', ' + sys.fn_replreplacesinglequoteplusprotectstring(@QualName1) collate database_default + ', ' +
isnull(convert(nvarchar, @column_tracking), 'NULL') + ', ' + convert(nvarchar, @trigger_type) +
', ''' + sys.fn_replreplacesinglequote(@viewname) collate database_default + '''' +
', ''' + sys.fn_replreplacesinglequote(@tsview) collate database_default + '''' +
', ''' + sys.fn_replreplacesinglequote(@trigname) collate database_default + ''''+
', ''' + sys.fn_replreplacesinglequote(@current_mappings_viewname) collate database_default + ''''+
', ''' + sys.fn_replreplacesinglequote(@past_mappings_viewname) collate database_default + '''' +
', ''' + sys.fn_replreplacesinglequote(@genhistory_viewname) collate database_default + ''''

if @using_logical_records = 1 or
(select sum(datalength(smsf.filtername) + 2 * datalength(smsf.join_filterclause)
+ 2 * datalength(smsf.join_articlename) + 2 * datalength(sma.name) + 100)
from sysmergesubsetfilters smsf join sysmergearticles sma
on smsf.art_nickname = sma.nickname
where smsf.art_nickname = @tablenick) > 1000

-- (select count(*) from dbo.sysmergesubsetfilters where art_nickname = @tablenick
-- or join_nickname = @tablenick) >= 10 -- arbitrary limit.
-- if there is a logical record then the update trigger has rows larger than nvarchar(4000)
-- if there are too many join filters on an article, the number of characters
-- in the sync view definition could be more than nvarchar(4000) which
-- xp_execresultset does not handle well. Use sp_execresultset_nvarcharmax instead,
-- but not always because it does not perform well because of nvarchar(max) perf.
begin
exec @retcode = sys.sp_execresultset_nvarcharmaxrows @cmd
if @@ERROR<>0 OR @retcode<>0 goto FAILURE
end
else
begin
exec @retcode = sys.xp_execresultset @cmd, @dbname
if @@ERROR<>0 OR @retcode<>0 goto FAILURE
end
end

if (@owner_is_admin = 1)
begin
declare @temp_qual_trigname nvarchar(300)
select @temp_qual_trigname = quotename(@owner) + '.' + quotename(@trigname)
exec sp_MS_marksystemobject @temp_qual_trigname
if @@error<>0 goto FAILURE
end

select @trigger_type = @trigger_type + 1
end


-- generate view here that uses the triggers
select @instrigid = object_id(quotename(@owner) + '.' + quotename(@instrigname))
select @updtrigid = object_id(quotename(@owner) + '.' + quotename(@updtrigname))
select @deltrigid = object_id(quotename(@owner) + '.' + quotename(@deltrigname))

-- check if there is a before image table. If so create a view from which public can select
select @before_name = OBJECT_NAME(before_image_objid) from sysmergearticles where objid = @objid
set @beforeview = 'MSmerge_bivw_' + @guidstr
if (@before_name is not null)
begin
-- Create the a view on the before image table if it doesn't already exist. --
-- this view is needed for security purposes since we don't want to grant rights
on the before image table to all users --
if object_id(@beforeview, 'V') is not NULL
begin
exec (' drop view dbo.' + @beforeview)
if @@ERROR<>0 goto FAILURE
end
if object_id(@beforeview, 'V') is NULL
begin
set @cmd = 'create view dbo.' + @beforeview + ' as
select * from dbo.'
+ @before_name + ' where
trigger_nestlevel('
+ convert(nvarchar,@instrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@updtrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@deltrigid) + ') > 0
with check option'

execute (@cmd)
if @@ERROR<>0 goto FAILURE
set @cmd = 'grant update, insert, select, delete on ' + @beforeview + ' to public'
execute (@cmd)
if @@ERROR<>0 goto FAILURE
execute sp_MS_marksystemobject @beforeview
if @@ERROR<>0 goto FAILURE
end
end

set @cmd = '
create view dbo.'
+ @viewname + ' as
select * from dbo.MSmerge_contents where
trigger_nestlevel('
+ convert(nvarchar,@instrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@updtrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@deltrigid) + ') > 0
with check option'


execute (@cmd)
if @@ERROR<>0 goto FAILURE
set @cmd = 'grant update, insert, select, delete on ' + @viewname + ' to public'
execute (@cmd)
if @@ERROR<>0 goto FAILURE
execute sp_MS_marksystemobject @viewname
if @@ERROR<>0 goto FAILURE

set @cmd = '
create view dbo. '
+ @tsview + ' as
select * from dbo.MSmerge_tombstone where
trigger_nestlevel('
+ convert(nvarchar,@instrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@updtrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@deltrigid) + ') > 0
with check option'

execute (@cmd)
if @@ERROR<>0 goto FAILURE
set @cmd = 'grant update, insert, select, delete on ' + @tsview + ' to public'
execute (@cmd)
if @@ERROR<>0 goto FAILURE
execute sp_MS_marksystemobject @tsview
if @@ERROR<>0 goto FAILURE

if (exists (select * from sysmergearticles where artid = @artid and pubid in (select pubid from sysmergepublications where use_partition_groups = 1)))
begin
set @cmd = '
create view dbo.'
+ @current_mappings_viewname + ' as
select * from dbo.MSmerge_current_partition_mappings where
trigger_nestlevel('
+ convert(nvarchar,@instrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@updtrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@deltrigid) + ') > 0
with check option'

execute (@cmd)
if @@ERROR<>0 goto FAILURE
set @cmd = 'grant update, insert, select, delete on ' + @current_mappings_viewname + ' to public'
execute (@cmd)
if @@ERROR<>0 goto FAILURE
execute sp_MS_marksystemobject @current_mappings_viewname
if @@ERROR<>0 goto FAILURE

set @cmd = '
create view dbo.'
+ @past_mappings_viewname + ' as
select * from dbo.MSmerge_past_partition_mappings where
trigger_nestlevel('
+ convert(nvarchar,@instrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@updtrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@deltrigid) + ') > 0
with check option'

execute (@cmd)
if @@ERROR<>0 goto FAILURE
set @cmd = 'grant update, insert, select, delete on ' + @past_mappings_viewname + ' to public'
execute (@cmd)
if @@ERROR<>0 goto FAILURE
execute sp_MS_marksystemobject @past_mappings_viewname
if @@ERROR<>0 goto FAILURE
end

set @cmd = '
create view dbo.'
+ @genhistory_viewname + ' as
select * from dbo.MSmerge_genhistory where
trigger_nestlevel('
+ convert(nvarchar,@instrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@updtrigid) + ') > 0 or
trigger_nestlevel('
+ convert(nvarchar,@deltrigid) + ') > 0
with check option'

execute (@cmd)
if @@ERROR<>0 goto FAILURE
set @cmd = 'grant update, insert, select, delete on ' + @genhistory_viewname + ' to public'
execute (@cmd)
if @@ERROR<>0 goto FAILURE
execute sp_MS_marksystemobject @genhistory_viewname
if @@ERROR<>0 goto FAILURE

if @recreate_repl_views = 1
begin
-- regenerate repl_view_ that uses triggers for this article @artid
select top 1 @pubid = pubid from dbo.sysmergearticles
where artid = @artid
order by pubid

while @pubid is not null
begin
exec @retcode = sys.sp_MScreate_article_repl_view @pubid, @artid
if @@error <> 0 or @retcode <> 0 goto FAILURE

select @prev_pubid = @pubid
select @pubid = NULL

select top 1 @pubid = pubid from dbo.sysmergearticles
where artid = @artid
and pubid > @prev_pubid
order by pubid
end
end
end
else
begin
-- Create trigger that disallows local updates/inserts/deletes.
exec @retcode= sys.sp_MScreatedownloadonlytriggers @artid= @artid
if @@error <> 0 or @retcode <> 0 goto FAILURE
end
end -- 0=@islightweight
else
begin
exec @retcode= sys.sp_MSdroplightweighttriggers @artid=@artid
if @@error <> 0 or @retcode <> 0 goto FAILURE

-- Find out whether this article is updateable at that node.
set @maintainsmetadata= sys.fn_MSarticle_allows_DML_at_this_replica(@artid, default)

if 1=@maintainsmetadata
begin
exec @retcode= sys.sp_MScreatelightweighttriggers @artid= @artid
if @@error <> 0 or @retcode <> 0 goto FAILURE
end
else
begin
-- Create trigger that disallows local updates/inserts/deletes.
exec @retcode= sys.sp_MScreatedownloadonlytriggers @artid= @artid
if @@error <> 0 or @retcode <> 0 goto FAILURE
end
end

exec @retcode = sys.sp_MSdropdisabledmltrigger @source_table_passed_in, @table_owner
if @@error <> 0 or @retcode <> 0
goto FAILURE

commit tran

return 0

FAILURE:
rollback tran sp_MSaddmergetriggers
commit tran
raiserror(20715, 10, -1, @source_table)
return 1
end

No comments:

Post a Comment

Total Pageviews