SQL SERVER – How to Enable or Disable All the Triggers on a Table and Database?

Here is the quickest way to disable all the triggers for a table. Please note that when it is about the table, you will have to specify the name of the table. However, when we have to enable or disable trigger on the database or server, we just have to specify word like database (and keep the current context of the database where you want to disable database) and all server.

Enable Triggers on a Table

ENABLE TRIGGER ALL ON TableName;  

Enable Triggers on a Database

ENABLE TRIGGER ALL On DATABASE;  

Enable Triggers on a Server

ENABLE TRIGGER ALL ON SERVER;  

Disable Triggers on a Table

DISABLE TRIGGER ALL ON TableName; 

Disable Triggers on a Database

DISABLE TRIGGER ALL ON DATABASE; 

Disable Triggers on a Server

DISABLE TRIGGER ALL ON SERVER;  

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Configure the Backup Compression Default Server Configuration Option
Next Post
Interview Question of the Week #035 – Different Ways to Identify the Open Transactions

Related Posts

11 Comments. Leave new

  • Krzysztof Białobrzeski
    September 6, 2015 2:04 am

    Hello Pinal Dave,

    Your query not disable all the triggers for a table.

    The query for disable all the trigers on table is:

    DISABLE TRIGGER ALL ON TableName;
    GO

    The query for disable all the trigers on database is:

    DISABLE TRIGGER ALL ON DATABASE;
    GO

    The query for enable all the trigers on table is:

    ENABLE TRIGGER ALL ON TableName;
    GO

    The query for enable all the trigers on database is:

    ENABLE TRIGGER ALL ON DATABASE;
    GO

    Reply
  • @Krzysztof, I think Pinal Dave included the word “safety” in all his scripts, to prevent them being run by accident. @Pinal it would be nice to get some verification – is that why you included “safety”?

    Reply
    • Yeah that was the goal but I See people getting confused so I have changed it back. I know you understood my point.

      Reply
  • Hi Pinal, ENABLE TRIGGER ON ALL TableName doesn’t seem to be working SQL Server 2012. However, ALTER TABLE [dbo].[TableName] ENABLE TRIGGER ALL; works fine.

    Reply
  • There is a syntax error in his code. Currently, the ENABLE section reads:

    —————————————————————
    ENABLE TRIGGER ON ALL TableName;
    GO

    ENABLE TRIGGER ON ALL DATABASE;
    GO
    —————————————————————

    However, the syntax is incorrect. The “ON” and “ALL” are reversed. Instead, it SHOULD BE:

    —————————————————————
    ENABLE TRIGGER ALL ON TableName;
    GO

    ENABLE TRIGGER ALL ON DATABASE;
    GO
    —————————————————————

    Reply
  • Ravjeet Singh
    July 10, 2018 8:23 am

    Does this work on SQL Server 2008 R2?

    Reply
  • William Payne
    August 7, 2018 1:13 am

    I have a trigger on one of my tables that has been disabled for some time. I ran an ALTER to it to tweak some code for future reference when I realized the trigger had been re-enabled. This happened on multiple environments regarding the same trigger. My question for you all: How is it possible for a disabled trigger to get re-enabled without manually doing it in Object Explorer or running an ENABLE script to do it?

    Reply
  • Thanks, that was helpful.

    Reply
  • Pinal, I appreciate that you shared this tip. I have come back to this page multiple times over the past year. My transaction logging triggers can be slow and these tips help me run maintenance queries a lot faster. Kudos to you!

    Reply

Leave a Reply

Menu