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)
13 Comments. Leave new
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
@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”?
Yeah that was the goal but I See people getting confused so I have changed it back. I know you understood my point.
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.
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
—————————————————————
Thanks for bringing to my attention, I fixed it.
Does this work on SQL Server 2008 R2?
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?
Thanks, that was helpful.
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!
It’s important to note that “DISABLE TRIGGER ALL ON DATABASE” will only disable the DDL triggers created on the database, and not the DML triggers on each table inside the database.
Very fair point.