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

Leave a Reply

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