Disable and Enable triggers in SQL Server

A DML trigger is a set of SQL statements which are executed when a DML (Insert/Update/Delete) event occurs in a database. A DDL trigger is set of SQL statements executed when a DDL event (Drop_Table/Alter_Table) occurs in a database. In this blog we’ll see how to disable or enable DML and DDL triggers.

To disable a DML trigger execute below query

USE AdventureWorks2014
GO
DISABLE TRIGGER HumanResources.dEmployee ON HumanResources.Employee

To check triggers status execute below query

SELECT name, is_disabled FROM sys.triggers WHERE name='dEmployee'

1_Disable and Enable triggers in SQL Server

The column value is_disabled = 1 as shown in above image indicates that trigger is disabled and is_disabled = 0 indicates that trigger is active.

To enable a DML trigger execute below query

ENABLE Trigger HumanResources.dEmployee ON HumanResources.Employee

To disable a DDL trigger execute below query

DISABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE

To enable DDL trigger execute below query

ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE

To check triggers status execute below query

2_Disable and Enable triggers in SQL Server

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *