Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Disable Triggers in SQL Server 2005


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

Problem
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?

Solution
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;
GO

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

GO

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;
GO

DISABLE TRIGGER HumanResources.dEmployee ON HumanResources.Employee;
GO
 

Validating the trigger has been disabled

USE AdventureWorks;
GO

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

Enabling the trigger with the ENABLE TRIGGER command

USE AdventureWorks;
GO

ENABLE TRIGGER HumanResources.dEmployee ON HumanResources.Employee;
GO
 

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


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Rajesh,

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.

HTH.

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

 Hi

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
GO 


ALTER TABLE HumanResources.dEmployee DISABLE TRIGGER ALL
GO

 

 

Enable all triggers in a table

 

USE AdventureWorks
GO 


ALTER TABLE HumanResources.dEmployee ENABLE TRIGGER ALL
GO


Learn more about SQL Server tools