Sometimes we need to disable trigger on a table especially when performing admin tasks on a table. Following commands provide a quick way to disable all the triggers for a table. Please note

that when we are diabling triggers on a table we will have to specify the name of the table. Howevere in case we are enabling/disabling on database or server , we just have to specify word

like database and all server.Also we need to keep the current context of the database where you want to disable database.

For enabling on a Table

copytext

ENABLE TRIGGER safety ON TableName;
GO

ENABLE TRIGGER safety ON TableName;
GO

For enabling on a Database

copytext

ENABLE TRIGGER ALL  safety ON DATABASE;
GO

ENABLE TRIGGER ALL  safety ON DATABASE;
GO

For enabling on a Server

copytext

ENABLE TRIGGER safety ON ALL SERVER;
GO

ENABLE TRIGGER safety ON ALL SERVER;
GO

For disabling on a Table

copytext

DISABLE TRIGGER safety ON TableName;
GO

DISABLE TRIGGER safety ON TableName;
GO

For disabling on a Database

copytext

DISABLE TRIGGER ALL safety ON DATABASE;
GO

DISABLE TRIGGER ALL safety ON DATABASE;
GO

For disabling on a Server

copytext

DISABLE TRIGGER safety ON ALL SERVER;
GO

DISABLE TRIGGER safety ON ALL SERVER;
GO

However, if you want to disable the trigger only for a particular statement there is no default mechanism to do this. Following is one custom approach using a temp table:

In this approach we create a temporary table before we execute the statement that would fire the trigger. Now the trigger will check for the existence of the temporary table and if it exists

the trigger will return and not execute the code. If the temporary table doe not exist trigger will execute its code as normal.

copytext

IF OBJECT_ID('dbo.Table1') IS NOT NULL 
DROP TABLE dbo.Table1 
GO 
CREATE TABLE dbo.Table1(ID INT) 
GO 
 
-- Creating a trigger 
CREATE TRIGGER TRTest ON dbo.Table1 
FOR INSERT,UPDATE,DELETE 
AS 
IF OBJECT_ID('tempdb..#Skip') IS NOT NULL RETURN 
 
--Remaninig Code
GO

IF OBJECT_ID('dbo.Table1') IS NOT NULL 
DROP TABLE dbo.Table1 
GO 
CREATE TABLE dbo.Table1(ID INT) 
GO 

-- Creating a trigger 
CREATE TRIGGER TRTest ON dbo.Table1 
FOR INSERT,UPDATE,DELETE 
AS 
IF OBJECT_ID('tempdb..#Skip') IS NOT NULL RETURN 

--Remaninig Code
GO

In case we do not want the trigger to fire for a statement,we simply need to create a temporary table in the statement as below.

copytext

CREATE TABLE #Skip(ID INT) 
-- Actual statement 
INSERT dbo.Table1 VALUES(10) 
DROP TABLE #Skip

CREATE TABLE #Skip(ID INT) 
-- Actual statement 
INSERT dbo.Table1 VALUES(10) 
DROP TABLE #Skip