April 16, 2012

sp_addsynctriggers (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_addsynctriggers(nvarchar @sub_table
, nvarchar @sub_table_owner
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @ins_proc
, nvarchar @upd_proc
, nvarchar @del_proc
, nvarchar @cftproc
, nvarchar @proc_owner
, nvarchar @identity_col
, nvarchar @ts_col
, nvarchar @filter_clause
, varbinary @primary_key_bitmap
, bit @identity_support
, bit @independent_agent
, nvarchar @distributor
, int @pubversion
, bit @dump_cmds)

MetaData:

 create procedure sys.sp_addsynctriggers   
(
@sub_table sysname, -- table name
@sub_table_owner sysname, -- table owner
@publisher sysname, -- publishing server name
@publisher_db sysname, -- publishing database name. If NULL then same as current db
@publication sysname, -- publication name.
@ins_proc sysname,
@upd_proc sysname,
@del_proc sysname,
@cftproc sysname,
@proc_owner sysname,
@identity_col sysname = 'NULL',
@ts_col sysname = 'NULL',
@filter_clause nvarchar(4000) = 'NULL',
@primary_key_bitmap varbinary(4000),
@identity_support bit = 0,
@independent_agent bit = 0
,@distributor sysname -- distribution server name
,@pubversion int = 1 -- 1 = when this call is generated by pre 80SP3 publishers, 2 = 80SP3 or later
,@dump_cmds bit = 0 -- 1 = dump sync trigger creation commands
)
AS
BEGIN
set nocount on

declare @db sysname
,@trigname sysname
,@ins_trig sysname
,@upd_trig sysname
,@del_trig sysname
,@dbname sysname
,@ccols int
,@cnt int
,@retcode int
,@cmd nvarchar(4000)
,@merge_pub_object_bit int
,@object_id int
,@constraint_name sysname
,@quoted_name nvarchar(540)
,@qualname nvarchar(540)
,@loctrancount int
,@ftscolnull bit
,@fidentcolnull bit
,@pubsecuritymode int
,@publogin sysname
,@pubencpassword nvarchar(524)
,@publisherlink sysname
,@updatemode int
,@old_id int
,@old_name sysname
,@parent_obj int

select @merge_pub_object_bit = 128
,@fidentcolnull = case when (@identity_col in ('null', 'NULL')) then 1 else 0 end
,@ftscolnull = case when (@ts_col in ('null', 'NULL')) then 1 else 0 end
,@quoted_name = N'msrepl_tran_version'

-- Security Check
EXEC @retcode = sys.sp_MSreplcheck_subscribe
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
--
-- validate @pubversion
--
if (@pubversion is null)
select @pubversion = 1
if (@pubversion not in (1,2))
return 1

if @dump_cmds = 1
begin
select @qualname = case when (lower(@sub_table_owner) = N'null')
then QUOTENAME(@sub_table)
else QUOTENAME(@sub_table_owner) + N'.' + QUOTENAME(@sub_table) end
if object_id(@qualname) is null
return (0)
end
--
-- Dist Agent executes this sproc with 'implicit transasctions on'.
-- We take care of our own transactions boundaries to get out of tran
--
set implicit_transactions off
select @loctrancount = @@trancount
while @@trancount > 0 commit tran

-- check valid server and database setting
-- 1. nested trigger have to be on
if exists (select * from master.dbo.sysconfigures where config = 115 and value = 0)
begin
raiserror(21081, 16, 1)
return (1)
end

-- 2. db option: recursive trigger have to be off
if DATABASEPROPERTYEX(db_name(), N'IsRecursiveTriggersEnabled') <> 0
begin
raiserror(21082, 16, 1)
return (1)
end

-- 2. db compatibility level have to be 7.0
if exists (select * from master.dbo.sysdatabases where dbid = db_id() and cmptlevel < 70)
begin
raiserror(21083, 16, 1)
return (1)
end
--
-- qualify the destination table
--
select @qualname = case when (lower(@sub_table_owner) = N'null')
then QUOTENAME(@sub_table)
else QUOTENAME(@sub_table_owner) + N'.' + QUOTENAME(@sub_table) end
--
-- begin transaction for the processing
--
BEGIN TRANSACTION
-- Verify that table exists
select @object_id = object_id (@qualname)
if not (@object_id is null)
begin
EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)
-- EXEC %%Object(MultiName = @qualname).LockExclusiveMatchID(ID = @object_id)
if @@error <> 0
select @object_id = null
end


if @object_id is null
begin
-- Save point not supported in distributed txn, explicitly or escalated
ROLLBACK TRANSACTION
raiserror(20507, 16, 1, @qualname, 'sp_addsynctriggers')
return (1)
end
-- Add default version guid column
-- The default constraint is transfered with snapshot already for native publication.
-- Need to detect to see if default constraint already there.
if @dump_cmds = 0
begin
if not exists (select *
from sysconstraints as con join sys.columns as col
on con.colid = col.column_id
and con.id = col.object_id
and OBJECTPROPERTY ( con.constid , 'IsDefaultCnst' ) = 1 -- default
and col.object_id = @object_id
and col.name = @quoted_name)
begin
select @constraint_name = 'MSrepl_tran_version_default_' + convert(nvarchar(10), @object_id)
exec ('alter table ' + @qualname +
' add constraint ' + @constraint_name +
' default newid() for ' + @quoted_name )
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end
end
end
--
-- determine if we should pass @cftproc to scripting of synctran trigger
-- Check if the subscription needs scripting for queued replication.
-- For publishers that are pre 80SP3 - cftproc should not be null
-- that takes care of it. For publisher that 80SP3 or later - we need
-- to validate the article information
--
-- Check the version of the publisher
--
if ((@pubversion = 2) and not (@cftproc is null or lower(@cftproc) = 'null'))
begin
--
-- Publisher is 80SP3 or later
-- @cftproc should exist and article information should exist
-- for this subscription
--
if not exists (select artid
from (dbo.MSsubscription_articles as a join dbo.MSsubscription_agents as b
on a.agent_id = b.id)
where UPPER(b.publisher) = UPPER(@publisher)
and b.publisher_db = @publisher_db
and b.publication = @publication
and a.dest_table = object_name(@object_id))
begin
--
-- There is no article information
-- disable queued specific scripting
--
select @cftproc = 'null'
end
end
--
-- Create the replication metadata tables for updating subscribers
--
if @dump_cmds = 0
begin
if (LOWER(@cftproc) = 'null')
begin
exec @retcode = sys.sp_MScreate_sub_tables_internal
@tran_sub_table = 1,
@property_table = 1,
@sqlqueue_table = 0
end
else
begin
exec @retcode = sys.sp_MScreate_sub_tables_internal
@tran_sub_table = 1,
@property_table = 1,
@sqlqueue_table = 1
end
if @@ERROR <> 0 or @retcode <> 0
begin
ROLLBACK TRANSACTION
return (1)
end
end
--
-- Processing for Publisher RPC info in MSsubscription properties
--
select @publogin = sp.publisher_login
,@pubencpassword = sp.publisher_password
,@pubsecuritymode = sp.publisher_security_mode
,@publisherlink = sp.publisherlink
,@updatemode = sa.update_mode
from MSsubscription_properties as sp join MSsubscription_agents as sa
on upper(sp.publisher) = upper(sa.publisher)
and sp.publisher_db = sa.publisher_db
and sp.publication = sa.publication
where upper(sa.publisher) = upper(@publisher)
and sa.publisher_db = @publisher_db
and sa.publication = @publication
--
-- Process only for Immediate updating cases
--
if (@updatemode in (1,3,5) and @dump_cmds = 0)
begin
--
-- if there is an old entry - refresh it
--
if (@pubsecuritymode is not null)
begin
--
-- we have an entry with publisher link information
--
if (@pubsecuritymode in (0,2) and @publisherlink is null)
begin
--
-- Shiloh format -we need to refresh
-- unencrypt the password
--
if (@pubencpassword is not null)
begin
exec @retcode = sys.sp_MSrepldecrypt @pubencpassword output
IF @@error <> 0 OR @retcode <> 0
begin
ROLLBACK TRANSACTION
return (1)
end
end
--
-- Refresh the link information
--
exec @retcode = sys.sp_link_publication
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@security_mode = @pubsecuritymode,
@login = @publogin,
@password = @pubencpassword,
@distributor = @distributor
if @@ERROR <> 0 or @retcode <> 0
begin
ROLLBACK TRANSACTION
return (1)
end
end
end -- @pubsecuritymode is not null
end -- @updatemode in (1,3,5)
--
-- table should not be part of merge replication
--
if exists (select * from sys.tables where
is_merge_published = 1 and
object_id = @object_id)
begin
ROLLBACK TRANSACTION
raiserror(21063, 16, 1, @qualname)
return (1)
end

--
-- Drop all replication triggers on the source object
-- We should drop all because we don't support updatable subscriptions to
-- multiple publications on same dest table.
--
declare #object_cursor CURSOR LOCAL FAST_FORWARD for
select o.object_name, st.object_id
from dbo.MSreplication_objects o
join sys.triggers st
on o.object_name = st.name
and st.parent_id = @object_id
and o.object_type = 'T'

OPEN #object_cursor
FETCH #object_cursor INTO @old_name, @old_id
WHILE (@@fetch_status <> -1)
BEGIN
-- Cleanup identity range table
select @parent_obj = 0
select @parent_obj = parent_object_id from sys.objects where object_id = @old_id
-- for @dump_cmds = 0, we're only regening triggers, so we don't clear out the identity range table
if object_id(N'dbo.MSsub_identity_range') is not null and @dump_cmds = 0
delete dbo.MSsub_identity_range where objid = @parent_obj

-- Drop the trigger
exec @retcode = sys.sp_MSdrop_object
@object_id = @old_id
if @retcode <> 0 or @@error <> 0
goto UNDO
delete from dbo.MSreplication_objects where object_name=@old_name
FETCH #object_cursor INTO @old_name, @old_id
END
CLOSE #object_cursor
DEALLOCATE #object_cursor

--
-- Generate trigger names
-- sp_addsynctriggerscore depends on these trigger name prefix, both to change both if need to
--
select @trigname = RTRIM(SUBSTRING(@sub_table,1,110))
select @ins_trig = N'trg_MSsync_ins_' + @trigname
,@upd_trig = N'trg_MSsync_upd_' + @trigname
,@del_trig = N'trg_MSsync_del_' + @trigname
--
-- check uniqueness of names and revert to ugly guid-based name if friendly name already exists
--
if object_id(@ins_trig, 'TR') is not NULL
or object_id(@upd_trig, 'TR') is not NULL
or object_id(@del_trig, 'TR') is not NULL
begin
declare @guid_name nvarchar(36)
select @guid_name = convert (nvarchar(36), newid())
select @ins_trig = N'trg_MSsync_ins_' + @guid_name
,@upd_trig = N'trg_MSsync_upd_' + @guid_name
,@del_trig = N'trg_MSsync_del_' + @guid_name
end
-- last cleanup before calling sp_addsynctriggerscore
-- remove trigger entries from MSreplication_objects that do not exist any more
delete dbo.MSreplication_objects
where (object_name in (@ins_trig,@upd_trig,@del_trig)
-- this allows us to do a name comparsision without schema qualification
or object_name not in (select name from sys.triggers))
and object_type = N'T'
if @@error <> 0
goto UNDO
--
-- call the the core proc to create triggers, this only happens during snapshot
-- afterwhich DDL will just post the core proc along
--
exec @retcode = sys.sp_addsynctriggerscore @sub_table = @sub_table
,@sub_table_owner = @sub_table_owner
,@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
,@ins_proc = @ins_proc
,@upd_proc = @upd_proc
,@del_proc = @del_proc
,@cftproc = @cftproc
,@proc_owner = @proc_owner
,@identity_col = @identity_col
,@ts_col = @ts_col
,@filter_clause = @filter_clause
,@primary_key_bitmap = @primary_key_bitmap
,@identity_support = @identity_support
,@independent_agent = @independent_agent
,@pubversion = @pubversion
,@ins_trig = @ins_trig
,@upd_trig = @upd_trig
,@del_trig = @del_trig
,@dump_cmds = @dump_cmds
-- omit @alter to use the default (0 -- creation mode)
if @retcode <> 0 or @@error <> 0
goto UNDO
--
-- Mark the table for warnings in BCP
--
EXEC %%Relation(ID = @object_id).SetSyncTranSubscribe(Value = 1)
--
-- commit tran
--
commit tran
--
-- Ignore errors.
--
exec sys.sp_MSsub_cleanup_orphans
--
-- restore the trancount if necessary
--
if (@loctrancount > 0)
begin
while (@@trancount < @loctrancount)
begin tran
end
--
-- all done
--
return (0)
UNDO:
if @@trancount <> 0
rollback tran
return(1)
END

No comments:

Post a Comment

Total Pageviews