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. The database and server options cover DDL triggers only, not the triggers on your tables.
Enable Triggers on a Table
ENABLE TRIGGER ALL ON TableName; Enable Triggers on a Database (DDL Triggers)
ENABLE TRIGGER ALL On DATABASE; Enable Triggers on a Server
ENABLE TRIGGER ALL ON ALL SERVER; Disable Triggers on a Table
DISABLE TRIGGER ALL ON TableName; Disable Triggers on a Database (DDL Triggers)
DISABLE TRIGGER ALL ON DATABASE; Disable Triggers on a Server
DISABLE TRIGGER ALL ON ALL SERVER; Before You Disable All the Triggers, Read This
One detail confuses many people. The DATABASE and ALL SERVER options work on DDL triggers, the ones that fire on events such as CREATE TABLE, and at the server level also on logon triggers. They do not touch the DML triggers that fire on INSERT, UPDATE and DELETE for each table. For those, you run the table level command for every table, and you can generate that list from sys.triggers with a simple SELECT.
Also remember that a disabled trigger stays disabled for everyone, not only for your session, until someone enables it again. While it is off, audit rows are not written and any rules the trigger enforces are skipped. That is why I always keep the enable statement in the same script, right after the work, and I run it even when the load fails.
To check the current state, run SELECT name, OBJECT_NAME(parent_id) AS TableName, is_disabled FROM sys.triggers in the database. Server level triggers are listed in sys.server_triggers.
Two more points. Disabling a table trigger needs ALTER permission on the table, and the database and server level triggers need higher permissions. Also, if a trigger was switched off on purpose long ago, the ALL option in ENABLE TRIGGER will turn it back on along with the others, so note which ones were already off before you begin.
If your goal is only a fast bulk load, look at BULK INSERT first. By default it does not fire triggers, so you may not need to switch anything off.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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?
Only option I can think of is to put a SQL Agent job to enable/disable them. Why would SQL do it automatically?
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.