How to Disable All the Triggers in SQL Server Database

Sometime we have requirement that we need to disable all the triggers in SQL Server Database. We can use Cursor in TSQL To loop through list of Triggers and then disable them.

The below script can be use to disable all the triggers in SQL Server Database.

USE [Database]

GO

 DECLARE @TriggerName AS VARCHAR(500) 
DECLARE @TableName AS VARCHAR(500) 
DECLARE @SchemaName AS VARCHAR(100) 
--Disable All Triggers in a Database in SQL Server
 DECLARE DisableTrigger CURSOR FOR
SELECT 
TBL.name                   AS TableName,
       
Schema_name(TBL.schema_id) AS Table_SchemaName,
       
TRG.name                   AS TriggerName 
FROM   sys.triggers TRG
       
INNER JOIN sys.tables TBL
               
ON TBL.OBJECT_ID = TRG.parent_id 
               AND TRG.is_disabled=0 
               AND TBL.is_ms_shipped=0
 
OPEN DisableTrigger 
FETCH Next FROM DisableTrigger INTO @TableName,@SchemaName,@TriggerName 
WHILE @@FETCH_STATUS = 0
  
BEGIN
      DECLARE 
@SQL VARCHAR(MAX)=NULL

      
SET @SQL='Disable Trigger ' + @TriggerName +' ON '+@SchemaName+'.'+@TableName

      
EXEC (@SQL)
      
PRINT 'Trigger ::' + @TriggerName + 'is disabled on '+@SchemaName+'.'+@TableName
      
PRINT @SQL

      
FETCH Next FROM DisableTrigger INTO @TableName,@SchemaName,@TriggerName
  
END

CLOSE 
DisableTrigger
DEALLOCATE DisableTrigger



To check if all the triggers are disabled correctly in SQL Server Database, use below query

SELECT TBL.name                   AS TableName,
       
Schema_name(TBL.schema_id) AS Table_SchemaName,
       
TRG.name                   AS TriggerName,
       
TRG.parent_class_desc,
       
CASE
         
WHEN TRG.is_disabled = 0 THEN 'Enable'
         
ELSE 'Disable'
       
END                        AS TRG_Status 
FROM   sys.triggers TRG
       
INNER JOIN sys.tables TBL
               
ON TBL.OBJECT_ID = TRG.parent_id 
              
AND trg.is_disabled=1 --use this filter to get Disabled Triggers

No comments:

Post a Comment