SQL SERVER – Disable All the Trigger of Current Database

I have previously written article about SQL SERVER – Disable All Triggers on a Database – Disable All Triggers on All Servers. This is alternate method to achieve the same task.

Following article is sent by Manish Kaushik. I recommend all of you to read original article along with this article for complete idea.

CREATE PROCEDURE [dbo].[DisableAllTriggers]
AS
DECLARE
@string VARCHAR(8000)
DECLARE @tableName NVARCHAR(500)
DECLARE cur CURSOR
FOR SELECT
name AS tbname FROM sysobjects WHERE id IN(SELECT parent_obj FROM sysobjects WHERE xtype='tr')
OPEN cur
FETCH next FROM cur INTO @tableName
WHILE @@fetch_status = 0
BEGIN
SET
@string ='Alter table '+ @tableName + ' Disable trigger all'
EXEC (@string)
FETCH next FROM cur INTO @tableName
END
CLOSE
cur
DEALLOCATE cur
GO
----To execute the SP

EXEC [DisableAllTriggers]

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

12 thoughts on “SQL SERVER – Disable All the Trigger of Current Database

  1. In the cursor, you may want to use the system view “sys.Triggers” to get a list of triggers in the context of the current database rather than the outdated SQL 2000 style of using sysobjects. sysobjects is only for backward compatibility with 2000 and should be avoided on any new development. At the very least – if you did not want to use sys.triggers then you should be using sys.objects on SQL 2005 and 2008.
    If development is on a 2000 instance then sysobjects is the way to go in order to get a list of triggers.

    For 2005 and 2008 you could use
    SELECT QUOTENAME(OBJECT_NAME(parent_id)) AS tbName from sys.triggers

    But this does not take into account tables that exist within other schemas other than dbo. You will just receive an error if you run that code on triggers within other schemas.

  2. How about using this
    sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
    sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

  3. sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
    sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

    This one is good, as written by rohit

  4. – Include the schema:

    DECLARE @string VARCHAR(8000)
    DECLARE @tableName NVARCHAR(500)
    DECLARE @Name NVARCHAR(500)
    DECLARE cur CURSOR
    FOR SELECT sys.objects.name as tbname, sys.schemas.name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
    WHERE sys.objects.object_id IN(SELECT parent_obj FROM sysobjects WHERE xtype=’tr’)
    OPEN cur
    FETCH next FROM cur INTO @tableName, @Name
    WHILE @@fetch_status = 0
    BEGIN
    SET @string =’Alter table ‘+ @Name +’.’ +@tableName + ‘ Disable trigger all’
    EXEC (@string)
    FETCH next FROM cur INTO @tableName, @Name
    END
    CLOSE cur
    DEALLOCATE cur
    GO

  5. Hi – I searched the blog and other sources but could not find a way to do this:
    Disable all the triggers on a specific table when a specific user is doing a specific maintenance task within the context of a .NET application.

    Any ideas gratefully received…

    Thanks

    Jerry

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  7. Also pleas always surround your table names with brackets. This helps those of us dangerous enough to use reserved words as table names and table names with spaces

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