Disable Triggers in SQL Server 2005

By:   |   Comments (5)   |   Related: > 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:

SQLServer2005Triggers 1

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:

SQLServer2005Triggers 2

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:

SQLServer2005Triggers 3

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:

SQLServer2005Triggers 4


Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, December 9, 2012 - 2:30:23 PM - Jeremy Kadlec Back To Top (20839)


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 7, 2012 - 7:14:57 AM - Rajesh Back To Top (20799)

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 (226)

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 (165)


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 (164)

Disable all triggers in a table 


USE AdventureWorks




Enable all triggers in a table


USE AdventureWorks


get free sql tips
agree to terms