HomeDatabaseSQL Server: Enable and Disable Triggers

SQL Server: Enable and Disable Triggers

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.


Vishwanath Dalvi
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.
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

LATEST REVIEWS

Recent Comments

error: Content is protected !!