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