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

1 comment:

  1. Hi Amir, Your way of explaining things is great. I love the way you are extending the learning to other individuals. Again Thanks.

    Just want to know which book you will suggest for advanced t-sql programming.

    ReplyDelete