SQL Server: Enable and Disable Triggers

Posted May 29, 2013 by Vishwanath Dalvi in Database, SQL Server

If we have a trigger and want to prevent it from running, we can either delete the trigger or disable it temporarily. With the trigger disabled, it will not function. We have three ways to enable/disable a trigger in SQL Server by using a graphical option or by using a query editor.

We will see two ways of disabling a trigger using a query editor.

In the example below, we will assume that we have a TRG_EmployeeHistory trigger already created on the Employee table.

Disabling/enabling a trigger with the ALTER TABLE option

Disable a Trigger

ALTER TABLE EMPLOYEE 
DISABLE TRIGGER TRG_EmployeeHistory


Enable a Trigger

ALTER TABLE EMPLOYEE
ENABLE TRIGGER TRG_EmployeeHistory



Specifying the trigger and the table name to enable/disable triggers directly

Disable a Trigger

DISABLE TRIGGER TRG_EmployeeHistory ON EMPLOYEE


Enable a Trigger

ENABLE TRIGGER TRG_EmployeeHistory ON EMPLOYEE



Using the Graphical Option to enable/disable a trigger

Follow the instructions below.

[+] Object Explorer
      [-] Database Name
          [-] Table Name
              [-] expand Triggers
                  [-] Right-click on the trigger name to enable/disable it.


 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

  • Nuthan Murarysetty

    nice job