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'
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
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
