Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_addsynctriggers

  No additional text.


Syntax
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 DATABASEPROPERTY(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

 
Last revision 2008RTM
See also

  sp_addsynctriggerscore (Procedure)
sp_link_publication (Procedure)
sp_MSarticle_synctran_commands (Procedure)
sp_MSrepl_addpublication (Procedure)
sp_MStran_altertable (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