Declarative Referential Integrity - Generate Commands to Enable/Disable Triggers

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        4 Jul 2012

Description

This SP will generate a list of commands to enable/disable all triggers in a database.
For SQL 2000 and earlier, only DML trigger commands will be  generated, but for SQL 2005 and later, DML and DDL trigger commands will be generated.

Not changes to trigger status will be made by the SP.


Code

Stored Procedure:


DROP PROCEDURE dbo.usp_GenerateEnableDisableTriggerCommands
GO

CREATE PROCEDURE dbo.usp_GenerateEnableDisableTriggerCommands
AS

/***************************************************************
Purpose:   To generate commands to enable or disable all triggers
          
           Scope depends on SQL Server version.
           For SQL 2000 and earlier, only DML triggers on tables are covered.
           For SQL 2005 and later, DML and DDL on both Database and Server are covered
          
           No changes are made by this storted procedure
          
Author:        ChillyDBA
History:   4 Jul 2012 - Initial Issue

****************************************************************/


DECLARE
  
@SQLVersionNumber       INT
  
  
SELECT
@SQLVersionNumber = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion')), 1, CHARINDEX('.', CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion')))-1))

-- if the version is before SQL 2005 (9.x.x.x)
IF @SQLVersionNumber < 9
BEGIN
  
-- generate code to disable all triggers (DML triggers only)
  
SELECT
      
'ALTER TABLE '
      
+ SCHEMA_NAME(so2.schema_id)
       +
'.'
      
+ so2.name
      
+ ' DISABLE TRIGGER ALL' AS 'DISABLE Trigger Commands'
  
FROM sys.objects so1
  
INNER JOIN sys.objects so2
      
ON so1.parent_object_id = so2.OBJECT_ID
  
WHERE so1.TYPE = 'TR'
  
ORDER BY so2.name
  

  
-- generate code to enable all triggers (DML triggers only)
  
SELECT
      
'ALTER TABLE '
      
+ SCHEMA_NAME(so2.schema_id)
       +
'.'
      
+ so2.name
      
+ ' ENABLE TRIGGER ALL' AS 'ENABLE Trigger Commands'
  
FROM sys.objects so1
  
INNER JOIN sys.objects so2
      
ON so1.parent_object_id = so2.OBJECT_ID
  
WHERE so1.TYPE = 'TR'
  
ORDER BY so2.name
  
END

ELSE

-- for all other versions
BEGIN
  
-- generate code to disable all triggers (DML and DDL triggers)
  
SELECT
      
'ALTER TABLE '
      
+ SCHEMA_NAME(so.schema_id)
       +
'.'
      
+ so.name
      
+ ' DISABLE TRIGGER ALL' AS 'DISABLE Trigger Commands'
  
FROM sys.triggers st
  
INNER JOIN sys.objects so
      
ON st.parent_id = so.OBJECT_ID
  
WHERE st.parent_class = 1
  
ORDER BY so.name


  
-- if there are DDL triggers defined
  
IF EXISTS (SELECT 1 FROM sys.triggers WHERE parent_class <> 1)
  
BEGIN
       SELECT
          
'DISABLE TRIGGER ALL ON DATABASE'  AS 'DISABLE ALL DDL Triggers in DB'

      
SELECT
          
'DISABLE TRIGGER ALL ON ALL SERVER'  AS 'DISABLE ALL DDL Triggers in Server'
  
END
  
  
  
-- generate code to enable all triggers (DML and DDL triggers)
      
SELECT
      
'ALTER TABLE '
      
+ SCHEMA_NAME(so.schema_id)
       +
'.'
      
+ so.name
      
+ ' ENABLE TRIGGER ALL' AS 'ENABLE Trigger Commands'
  
FROM sys.triggers st
  
INNER JOIN sys.objects so
      
ON st.parent_id = so.OBJECT_ID
  
WHERE st.parent_class = 1
  
ORDER BY so.name

  
-- if there are DDL triggers defined
  
IF EXISTS (SELECT 1 FROM sys.triggers WHERE parent_class <> 1)
  
BEGIN
       SELECT
          
'ENABLE TRIGGER ALL ON DATABASE'  AS 'ENABLE ALL DDL Triggers in DB'

      
SELECT
          
'ENABLE TRIGGER ALL ON ALL SERVER'  AS 'ENABLE ALL DDL Triggers in Server'
  
END
END



Test Code:

 

USE AdventureWorksDW
GO

EXEC dbo.usp_GenerateEnableDisableTriggerCommands

ċ
usp_GenerateEnableDisableTriggerCommands.sql
(3k)
Andy Hughes,
Jul 4, 2012, 9:57 AM
Comments