Disable Triggers in SQL Server 2005

By:   |   Updated: 2007-11-27   |   Comments (5)   |   Related: More > Triggers

We were just working through a deployment and ran into a snag which caused us to run over the allocated amount of down time.  To speed up the some of the upcoming steps in the process we dropped a few triggers on our larger tables.  We did this to prevent the large number of inserts into the audit tables from extending the down time even further.  Not having the trigger fire for this process was something we were willing to sacrifice.  Unfortunately, once we released the system to production we no longer had the triggers and had to conduct a manual review to ensure we had all of our triggers.  Missing the data once we went to production was a major issue on top of having to extend the down time.  Do you have any suggestions on how to better manage the triggers in this scenario?

Yes - Let's break down this scenario a little bit to see how this problem can be prevented in the future.  First and foremost, make sure you test the process and have a good sense of the time needed to complete the process.  Along the same lines, be sure to have a rollback plan and have a sense of how much time will be needed to execute the rollback plan.  If you hit a snag, at times it is better to rollback rather than extend the downtime window, but that is really a decision you need to make based on the situation.  If the snag is big enough, it is better to keep the business up and running rather than sacrificing a long outage.  Hopefully these few items will help you prevent this type of issue.

Next, as far as not having the triggers fire during a mass update, once again that is something you need to make a decision on based on the context of the situation.  Keep in mind that although not having the triggers run may have helped your process, but the triggers have been created on the table for a reason.  The reasons could be for a business audit, internal compliance needs or reporting purposes.  I would make sure you balance the business needs with the immediate technical needs to determine the proper decision if you are faced with another mass update in the future.

Now, let's jump into the SQL Server trigger details and dig into another alternative to dropping a trigger, that is disabling a trigger.  With SQL Server 2005 now you have the ability to disable and enable triggers to prevent them from running as opposed to dropping the trigger.  The DISABLE TRIGGER and ENABLE TRIGGER commands are available for DML, DDL or logon triggers.  When a trigger is disabled, it remains as a database object, but does not execute based on the programmed condition. 

Here are the steps to walk through identifying and managing triggers with T-SQL commands:

T-SQL Trigger Management in SQL Server 2005

Determining the triggers in a database schema with sys.triggers (Object Schema View)

USE AdventureWorks;

FROM sys.triggers
WHERE is_disabled = 0 -- Enabled
ORDER BY [Name];


The first trigger in the query above is HumanResources.dEmployee.  Let's use that trigger as the example in the remainder of this tip.

Note - The key column in the sys.triggers view in this context is the is_disabled column.

Disabling a trigger with the DISABLE TRIGGER command

USE AdventureWorks;

DISABLE TRIGGER HumanResources.dEmployee ON HumanResources.Employee;

Validating the trigger has been disabled

USE AdventureWorks;

FROM sys.triggers
WHERE is_disabled = 1 -- Disabled
ORDER BY [Name];

Enabling the trigger with the ENABLE TRIGGER command

USE AdventureWorks;

ENABLE TRIGGER HumanResources.dEmployee ON HumanResources.Employee;

Now let's walk through the same steps with SQL Server 2005 Management Studio:

Trigger Management in SQL Server 2005 Management Studio

Navigate to the trigger

In this circumstance, let's navigate to the following location in Management Studio: instance root | Databases | Tables | HumanResources.Employee| Triggers.  This image displays the enabled trigger:


Disable the trigger

To disable the trigger right click on the dEmployee entry and select 'Disable.'  The screen below will display the status of change:


Validating the trigger has been disabled

When a trigger is deleted a small red down arrow will be displayed in the bottom right side of the icon as show below:


Enable the trigger

To enable the trigger right click on the dEmployee entry and select 'Enable.'  The screen below will display the status of change:



Next Steps


Last Updated: 2007-11-27

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips

Comments For This Article

Sunday, December 09, 2012 - 2:30:23 PM - Jeremy Kadlec Back To Top


The process and code outlined in this tip relate to disabling a trigger for a single table.  Triggers are either enabled or disabled for all transactions on a table.


Thank you,
Jeremy Kadlec

Friday, December 07, 2012 - 7:14:57 AM - Rajesh Back To Top

Disable / Enable triggers will be transaction level or globel?, if i am doing some buld upload or download, i need disable a trigger but it should not affect regular activity, which user doing in UI(front end).

Friday, January 18, 2008 - 2:26:12 PM - admin Back To Top

singh and aprato,

Thank you for both sides of the coin on this issue.

Thank you,
The MSSQLTips.com Team

Tuesday, December 18, 2007 - 11:23:57 AM - aprato Back To Top


You certainly can disable all triggers but just be careful.  Remember that it will disable them for all users accessing the database.

If users are accessing the database, you may still wish certain other triggers to fire while the problematic trigger is disabled.

Monday, December 17, 2007 - 1:52:20 PM - singh Back To Top

Disable all triggers in a table 


USE AdventureWorks




Enable all triggers in a table


USE AdventureWorks



Recommended Reading

SQL Server Trigger Example

SQL Server Trigger After Update for a Specific Value

How do SQL Server Triggers Work for Insert, Update, Delete and Truncate

Create a Simple SQL Server Trigger to Build an Audit Trail

Understanding SQL Server inserted and deleted tables for DML triggers

get free sql tips
agree to terms

Learn more about SQL Server tools