How to change existing triggers to be with 'not for replication' in merge replication in SQL Server?

Question

How to make a script that will change all triggers in a database to be NOT FOR REPLICATION? Is there a system procedure for it or we need to parse its definition and inject NOT FOR REPLICATION?

Answer 1

Yes, manually, but first I would try this way:

proc [dbo].[db_compare_make_trigger_NOT_FOR_REPLICATION_sp]
as

declare cur cursor fast_forward for
select st.name, definition, is_disabled, OBJECT_NAME(parent_id)
from sys.triggers st join
    sys.sql_modules sm on st.object_id = sm.object_id
where is_ms_shipped = 0 
    and is_not_for_replication = 0
    and parent_id > 0

declare @name nvarchar(127), @definition nvarchar(max), @is_disabled bit, @table nvarchar(127)

open cur

fetch next from cur into @name, @definition, @is_disabled, @table

while @@FETCH_STATUS = 0
begin
    declare @sql nvarchar(max) = null

    declare @name_bckp nvarchar(127) = '__' + @name + N'_' + replace(replace(replace(replace(CONVERT(nvarchar,getdate(), 126), '-', '_'), ':', '_'), '.', '_'), 'T', '_')
    PRINT @NAME + ' ON ' + @table
    set @sql = dbo.RegExReplace(@definition, 'AS\s+BEGIN', ' NOT FOR REPLICATION' + CHAR(13) + CHAR(10) + 'AS' + CHAR(13) + CHAR(10) + 'BEGIN')
    if charindex('NOT FOR REPLICATION', @sql) = 0
        set @sql = dbo.RegExReplace(@definition, 'AS\s+SET NOCOUNT ON', ' NOT FOR REPLICATION' + CHAR(13) + CHAR(10) + 'AS' + CHAR(13) + CHAR(10) + 'SET NOCOUNT ON')
    if charindex('NOT FOR REPLICATION', @sql) > 0
    begin try
        --BCKP it
        exec sys.sp_rename @NAME, @name_bckp
        set @definition = 'DISABLE TRIGGER [' + @name_bckp + '] ON [' + @table + ']'
        execute sp_executesql @definition
        --create it
        execute sp_executesql @sql
        --set previous state
        if @is_disabled = 1
            set @definition = 'DISABLE TRIGGER [' + @NAME + '] ON [' + @table + ']'
        else
            set @definition = 'ENABLE TRIGGER [' + @NAME + '] ON [' + @table + ']'
        execute sp_executesql @definition
        set @definition = 'DROP TRIGGER [' + @name_bckp + ']'
        execute sp_executesql @definition
        print 'DONE!'
    end try
    begin catch 
        declare @msg nvarchar(4000) = ERROR_MESSAGE()
        declare @esv int = error_severity()
        declare @est int = error_state()
        declare @lin int = error_line()

        insert into db_log (dateCreated, msg, Level, State, Line, additional)
        values(getdate(), @msg, @esv, @est, @lin, @NAME)
        print 'ERROR check db_log: ' + @msg
    end catch

    fetch next from cur into @name, @definition, @is_disabled, @table
end
close cur
deallocate cur

And remained triggers I would do manually.

for this to work you will need: CLR Assembly RegEx Functions for SQL Server