create procedure sys.sp_MSrepltrigpreparecert
(
    @mode tinyint    -- 1 = create/refresh, 2 = drop
    ,@certname sysname -- certificate name
    ,@publisher sysname  -- publisher
    ,@publisher_db sysname -- publisher db
    ,@publication sysname -- publication
)
with execute as 'dbo'
as
begin
    set nocount on
    declare @retcode int
                ,@certpwd sysname
                ,@cmd nvarchar(4000)
                ,@objectname nvarchar(517)
                ,@object_id int
                ,@ccertname sysname
                ,@mssql_data_path nvarchar(255)

    
    -- validate @mode
    
    if (@mode not in (1,2))
        return 1
    
    -- Do we already have a certificate
    
    select distinct @ccertname = 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 (@ccertname is not null)
    begin
        
        -- if we are in drop mode, the name should match
        
        if @mode = 2 and @certname != @ccertname
            return 1
    end
    
    -- declare a cursor for
    -- selecting the triggers for this publication
    
    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'
    
    -- process drop first
    
    if (@ccertname is not null)
    begin
        open #hctriggers
        fetch #hctriggers into @objectname, @object_id
        while (@@fetch_status = 0)
        begin
            
            -- if the trigger is signed with certificate - drop it
            
            if exists (select cp.thumbprint
                        from sys.crypt_properties cp join sys.certificates c
                            on cp.thumbprint = c.thumbprint
                        where c.name = @ccertname
                            and cp.major_id = @object_id)
            begin
                select @cmd = N'drop signature from ' + @objectname + N' by certificate ' + quotename(@ccertname)
                exec (@cmd)
                if (@@error != 0)
                    return 1
            end
            
            -- get the next trigger
            
            fetch #hctriggers into @objectname, @object_id
        end
        close #hctriggers
        
        -- drop the certificate
        
        if cert_id(@ccertname) is not null
        begin
            select @cmd = N'drop certificate ' + quotename(@ccertname)
            exec (@cmd)
            if (@@error != 0)
                return 1
        end
    end -- if (@ccertname is not null)
    
    -- if drop mode - we are done
    
    if (@mode = 2)
    begin
        deallocate #hctriggers
        return 0
    end
    
    
    -- create the certificate
    
    select @certpwd = cast(newid() as sysname) + '~Repl~' + cast(newid() as sysname)
    select @cmd = N'create certificate ' + quotename(@certname)
        + N' encryption by password = ''' + @certpwd + N''' '
        + N' with subject =  ''Replication certificate for sync trigger''  '
    exec (@cmd)
    if (@@error != 0)
        return 1
    
    -- select the triggers for this publication
    
    open #hctriggers
    fetch #hctriggers into @objectname, @object_id
    while (@@fetch_status = 0)
    begin
        
        -- sign the trigger with certificate
        
        select @cmd = N'add signature to ' + @objectname
            + N' by certificate ' + quotename(@certname) + N' with password = ''' + @certpwd + N''' '
        exec (@cmd)
        if (@@error != 0)
            return 1
        
        -- get the next trigger
        
        fetch #hctriggers into @objectname, @object_id
    end
    close #hctriggers
    deallocate #hctriggers
    
    -- done with signing
    -- drop the private key of the certificate
    
    select @cmd = N'alter certificate ' + quotename(@certname) + N' remove private key '
    exec (@cmd)
    if (@@error != 0)
        return 1
    
    -- backup certificate for import
    -- Note -if we can copy certificates across databases - this will not be needed anymore
    
    exec @retcode = sys.sp_MSget_setup_paths @data_path = @mssql_data_path output
    if (@retcode != 0 or @@error != 0 or @mssql_data_path = N'')
        return 1
    select @cmd = N'backup certificate ' + quotename(@certname) + N' to file = ''' + sys.fn_replreplacesinglequote(@mssql_data_path + N'\DATA\' + @certname) + '.cer'' '
    exec (@cmd)
    if (@@error != 0)
        return 1
    
    -- all done
    
    return 0
end