Computers, Programming, Technology, Music, Literature

TSQL: Enable or Disable all triggers in a database

leave a comment »

This article was originally published for www.prowareness.com and could be located at http://www.prowareness.com/blog/tsql-enable-or-disable-all-triggers-in-a-database/

Problem statement:

You are doing a lot of data fix for a corrupted database, but then when you are executing your ‘salvage script’ to restore the lost/corrupted data, amongst the events that get executed during a DML statement execution, triggers are one one of them. Sometimes you may find the triggers execution disturbing, and you would like to disable them. You may never know, the below code snippets might come in handy to enable and disable all triggers in a database.

Solution: [Ctrl+C, Ctrl+V style]

Copy, paste, and run the below.


Enable all triggers in a Microsoft SQL Server database
 

DECLARE @DisableTriggerCommand NVARCHAR(4000)
DECLARE @TriggerTableName NVARCHAR(500)

DECLARE DisableTriggerCursor CURSOR
FOR
(
		SELECT NAME AS TableName
		FROM sysobjects
		WHERE id IN (
				SELECT parent_obj
				FROM sysobjects
				WHERE xtype = 'tr'
				)
		)

OPEN DisableTriggerCursor

FETCH NEXT
FROM DisableTriggerCursor
INTO @TriggerTableName

WHILE @@fetch_status = 0
BEGIN
	SET @DisableTriggerCommand = N'ALTER TABLE ' + @TriggerTableName + ' ENABLE TRIGGER ALL'

	PRINT 'Executing:   ' + @DisableTriggerCommand + CHAR(13)

	EXECUTE sp_executesql @DisableTriggerCommand

	FETCH NEXT
	FROM DisableTriggerCursor
	INTO @TriggerTableName
END

CLOSE DisableTriggerCursor

DEALLOCATE DisableTriggerCursor

 

Disable all triggers in a Microsoft SQL Server database 

DECLARE @DisableTriggerCommand NVARCHAR(4000)
DECLARE @TriggerTableName NVARCHAR(500)

DECLARE DisableTriggerCursor CURSOR
FOR
(
		SELECT NAME AS TableName
		FROM sysobjects
		WHERE id IN (
				SELECT parent_obj
				FROM sysobjects
				WHERE xtype = 'tr'
				)
		)

OPEN DisableTriggerCursor

FETCH NEXT
FROM DisableTriggerCursor
INTO @TriggerTableName

WHILE @@fetch_status = 0
BEGIN
	SET @DisableTriggerCommand = N'ALTER TABLE ' + @TriggerTableName + ' DISABLE TRIGGER ALL'

	PRINT 'Executing:   ' + @DisableTriggerCommand + CHAR(13)

	EXECUTE sp_executesql @DisableTriggerCommand

	FETCH NEXT
	FROM DisableTriggerCursor
	INTO @TriggerTableName
END

CLOSE DisableTriggerCursor

DEALLOCATE DisableTriggerCursor

 

:q

Advertisements

Written by gmaran23

February 6, 2014 at 9:46 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: