By: Jeremy Kadlec | Updated: 2007-11-27 | Comments (5) | Related: > 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
- Triggers can offer a great deal of value to meet a number of business, regulatory and technical needs. Just be sure to keep them in mind as you maintain or change your application. The triggers are created for a reason and do meet a critical business need in most cases.
- As with most things 'the devil is in the details', so be sure to look for the little details that can cause you a great deal of issues moving forward with future deployments.
- The next time you have a rollout, be sure to consider these resources:
- Check out these related resources:
About the author
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
Article Last Updated: 2007-11-27
