How to Disable/Enable DDL and DML Triggers in SQL Server

In this post, we will learn how to Disable/Enable DDL (Data Definition Language) Triggers and DML ( Data Manipulation Language) Triggers.

The below code will walk you through creating sample tables, DML trigger and then finally how to enable or disable.

--Create SalePerson Table
CREATE TABLE dbo.SalePerson(
  SalePersonID INT PRIMARY KEY,
  FirstName VARCHAR(25),
  LastName VARCHAR(25),
)
GO
--Create History Table so we can insert records from Trigger
SELECT * INTO dbo.SalePersonHistory FROM dbo.SalePerson

GO--Create DML( Insert) Trigger 
CREATE TRIGGER dbo.Trg_InsertSalePerson
ON dbo.SalePerson
AFTER INSERT AS
BEGIN
INSERT INTO dbo.SalePersonHistory
SELECT * FROM INSERTED
END
GO
--Insert values for Test
INSERT INTO dbo.SalePerson VALUES (1,'Aamir','Shahzad')
--Check if Trigger is working correctly
SELECT * FROM dbo.SalePerson
SELECT * FROM dbo.SalePersonHistory

--Disable DML Trigger
Disable TRIGGER [Trg_InsertSalePerson] ON [dbo].[SalePerson]
--Insert record to see if trigger is disabled
INSERT INTO dbo.SalePerson VALUES (3,'John','Mike')

--Check if DML Trigger was disabled  correctly
SELECT * FROM dbo.SalePerson
SELECT * FROM dbo.SalePersonHistory
--Enable DML Trigger
Enable TRIGGER [Trg_InsertSalePerson] ON [dbo].[SalePerson]



DDL Triggers get fired on DDL events such as create object, drop, alter etc. The below code can be used for sample, When a new table is created. Select the statement it used and then print the message. At the end of Script you will learn how to Enable/Disable DDL Triggers.

--Create DDL Trigger
CREATE TRIGGER DDL_Trigger_Table
ON ALL SERVER FOR CREATE_Table
AS
    PRINT 'Table Created Successfully.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO

--Test if DDL Trigger working correctly
CREATE TABLE dbo.Test4(id INT)

--Disable DDL Trigger
Disable TRIGGER [DDL_Trigger_Table]  ON ALL SERVER

--Enable DDL Trigger
Enable TRIGGER [DDL_Trigger_Table]  ON ALL SERVER