1
Vote

Enable/Disable all/one scdc trigger(s)

description

I felt the need to be able to turn on/off the standardCDC triggers either singularly or en mass and the need to share it.
In order to loop through the tables with CDC triggers applied without using cursors (I avoid cursors wherever possible) I added an identity col to the [scdc].[change_tables] table. I hope you don't mind this slight amendment.
Those with <SS2008 just watch out for the inline declaration and value assignation.
 
/*
Proc dbo.sp_StandardCDC_Set_Trigger
Author: Derek Robinson
Version: 1.0
Purpose: To enable/disable all or one of the standardCDC triggers
 
Usage:
-- disable one table's SCDC trigger (acd)
exec sp_StandardCDC_Set_Trigger @tableName='acd',@enable=0
-- enable all standardCDC triggers
exec sp_StandardCDC_Set_Trigger @enable=1
*/
create proc sp_StandardCDC_Set_Trigger
@enable bit = 1,
@tableName sysname = '*'
as
declare @maxID int,
        @id int =0,
        @trigName sysname,
        @sql1 nvarchar(max),
        @sql2 nvarchar(max)
 
if @enable =1
    set @sql1 = 'ENABLE TRIGGER '
else
    set @sql1 = 'DISABLE TRIGGER '

select @maxID = MAX(id) from scdc.change_tables
if @tableName = '*'
begin
    while @id<>@maxID
    begin
        select top 1 @id = id, @tableName = name from scdc.change_tables
            where id > @id
            order by id asc
        select @trigName = 'tr'+@tableName
        -- create dynamic sql 
        set @sql2 = @sql1 +@trigName+' ON dbo.'+@tableName;
        EXECUTE sp_executeSQL @sql2
    end
end
else
begin
    set @sql2 = @sql1 +  'tr'+@tableName+' ON dbo.'+@tableName;
    EXECUTE sp_executeSQL @sql2
end

comments