Disable database triggers in Microsoft SQL databases

If you need to temporarily disable triggers in Microsoft SQL databases during database maintenance use the following script
--Disable triggers on all tables
DECLARE @enable BIT = 0;
DECLARE @trigger SYSNAME;
DECLARE @table SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name,
table_object.name table_name
FROM sysobjects trigger_object
JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id
WHERE trigger_object.type = 'TR';
OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;
WHILE @@FETCH_STATUS = 0 BEGIN
IF @enable = 1
SET @cmd = 'ENABLE ';
ELSE
SET @cmd = 'DISABLE ';
SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' ';
EXEC (@cmd);
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;
END
CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;
GO

Leave a Reply