Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepltrigredefine

  No additional text.


Syntax
create procedure sys.sp_MSrepltrigredefine
(
    @security_mode int
    ,@islocalpub bit  -- local or remote publisher
    ,@contextuser sysname -- context name
    ,@publisher sysname  -- publisher
    ,@publisher_db sysname -- publisher db
    ,@publication sysname -- publication
)
as
begin
    set nocount on
    declare @retcode int
                ,@certname sysname
                ,@objectname nvarchar(517)
                ,@object_id int
                ,@procdef nvarchar(max)
                ,@cmd nvarchar(max)
                ,@offsetwithstmt int
                ,@offsetforstmt int
                ,@offsetonstmt int
                ,@triglen bigint
                ,@rpcproc nvarchar(256)
                ,@dbname sysname

    
    -- proceed only if needed
    
    if (object_id('dbo.MSreplication_objects') is null)
        return 0
    
    -- check if we have any certificate associate with the replication triggers
    
    select distinct @certname = c.name
    from sys.crypt_properties cp join sys.certificates c
        on cp.thumbprint = c.thumbprint
    where object_name(cp.major_id) in
        (select object_name
            from dbo.MSreplication_objects
            where upper(publisher) = upper(@publisher)
                and publisher_db = @publisher_db
                and publication = @publication
                and object_type = 'T')
    if (@certname is not null)
    begin
        
        -- drop cert on publisher db only if the database name matches with
        -- with subscriber db (local db) in the certificate
        -- when we attach subscriber databases the certificates will contain
        -- the old subscriber db name and we cannot drop these on publisher db
        
        select @dbname = db_name()
        if (substring(@certname, 10, len(@dbname)) = @dbname)
        begin
            if (@islocalpub = 0)
            begin
                select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
                exec @rpcproc @mode = 2
                                    ,@islocalpub = @islocalpub
                                    ,@certname = @certname
                                    ,@targetdb = N'master'
            end
            else
            begin
                select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
                exec @rpcproc @mode = 2
                                    ,@islocalpub = @islocalpub
                                    ,@certname = @certname
                                    ,@targetdb = @publisher_db
            end
        end
        
        -- drop cert on subscriber db
        
        exec sys.sp_MSrepltrigpreparecert @mode = 2
                                    ,@certname = @certname
                                    ,@publisher = @publisher
                                    ,@publisher_db = @publisher_db
                                    ,@publication = @publication
    end
    
    -- For each trigger
    
    declare #hctriggers cursor local fast_forward for
        select quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name), o.object_id
        from dbo.MSreplication_objects as ro
            join sys.objects as o
        on ro.object_name = o.name
            and upper(ro.publisher) = upper(@publisher)
            and ro.publisher_db = @publisher_db
            and ro.publication = @publication
            and ro.object_type = 'T'
    open #hctriggers
    fetch #hctriggers into @objectname, @object_id
    while (@@fetch_status = 0)
    begin
        
        -- Get the trigger definition
        
        select @procdef = null
        select @procdef = definition
        from sys.sql_modules
        where object_id = @object_id
        if (@procdef is null)
        begin
            
            -- trigger does not exist
            -- skip and continue
            
            fetch #hctriggers into @objectname, @object_id
            continue
        end

        select @triglen = len(@procdef)
                ,@offsetonstmt = charindex(N'on', @procdef)
                ,@offsetforstmt = charindex(N'for', @procdef)
                ,@offsetwithstmt = charindex(N'with execute', @procdef)
        -- select @objectname = rtrim(ltrim(substring(@procdef, 15, @offsetonstmt - 15)))
        
        -- Process based on security mode
        
        if (@security_mode = 0)
        begin
            
            -- we have to include with execute = @contextuser in the definition
            
            select @cmd = N'drop trigger ' + @objectname
            exec(@cmd)
            if (@@error != 0)
                return 1
            select @cmd = case when (@offsetwithstmt = 0) then substring(@procdef, 1, @offsetforstmt - 1)
                                    else substring(@procdef, 1, @offsetwithstmt - 1) end
            select @cmd = @cmd + N'with execute as N''' + sys.fn_replreplacesinglequote(@contextuser) + N''' ' + substring(@procdef, @offsetforstmt, @triglen)
            exec(@cmd)
            if (@@error != 0)
                return 1
        end
        else
        begin
            
            -- we have to remove the with execute as if it exists
            
            if (@offsetwithstmt != 0)
            begin
                select @cmd = N'drop trigger ' + @objectname
                exec(@cmd)
                if (@@error != 0)
                    return 1
                select @cmd = substring(@procdef, 1, @offsetwithstmt - 1)
                    + substring(@procdef, @offsetforstmt, @triglen)
                exec(@cmd)
                if (@@error != 0)
                    return 1
            end
        end
        
        -- fetch the next trigger
        
        fetch #hctriggers into @objectname, @object_id
    end
    close #hctriggers
    deallocate #hctriggers
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

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