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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks
Next Post
SQLAuthority News – Find Your IP Address – What Is My IP Address

Related Posts

12 Comments. Leave new

  • 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.

    Reply
  • Rama Krishna.K.S.
    September 16, 2008 3:38 pm

    I think the above(DISABLE ALL TRIGGERS ON THE TABLE) can also be deone with ALTER TABLE statement also.

    Reply
  • How about using this
    sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
    sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

    Reply
    • You can use
      However note that they are all undocumented and may be removed from future release of SQL Server

      Reply
  • sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
    sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

    This one is good, as written by rohit

    Reply
  • How do we get the altered table name, time of altered, using trigger.

    Reply
  • Ok, so i have disabled the triggers, how does enable it back using the above script?

    Reply
  • — 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

    Reply
  • 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

    Reply
  • Charles Kincaid
    September 5, 2013 4:28 am

    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

    Reply
  • This is a very good script and running fine in demo version database that I have created but it is not working in my project database as i think because after disable procedure runs it is showing no of database tables by running the script in which the tables set comes with the no of triggers with the values disabled with “NULL”. Please solve my problem there very bunch of triggers exist.

    Reply

Leave a Reply

Menu