How to Enable Multiple Triggers in SQL Server Database

Let's say we have multiple triggers those are disabled in SQL Server Database and we want to enable all of the triggers. We can use the below script to enable all the triggers which are disable in SQL Server Database.

USE [Database]
GO
 
DECLARE @TriggerName AS VARCHAR(500)
 DECLARE @TableName AS VARCHAR(500)
 DECLARE @SchemaName AS VARCHAR(100)
 --Enable All Disabled Triggers in a Database in SQL Server 
DECLARE EnableTrigger 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=1
              AND TRG.is_ms_shipped=0 

OPEN EnableTrigger 

FETCH Next FROM EnableTrigger INTO @TableName,@SchemaName,@TriggerName 
WHILE @@FETCH_STATUS = 0
  
BEGIN
      DECLARE 
@SQL VARCHAR(MAX)=NULL

      
SET @SQL='Enable Trigger ' + @TriggerName +' ON '+@SchemaName+'.['+@TableName+']'
      P
RINT @SQL
      EXEC (@SQL)
      
PRINT 'Trigger ::' + @TriggerName + 'is Enabled on '+@SchemaName+'.'+@TableName
     

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

CLOSE 
EnableTrigger 
DEALLOCATE EnableTrigger


 Check if all triggers are enabled in SQL Server Database by above script correctly.

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=0 --use this filter to get Enabled/Active Triggers

1 comment: