Disable Trigger

DISABLE TRIGGER (Transact-SQL):

Disables a trigger.

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }

ON { object_name | DATABASE | ALL SERVER} [ ; ]

Arguments

Schema_name

Is the name of the schema to which the trigger belongs.  Schema_name cannot be specified for DDL or logon triggers.

Trigger_name

Is the name of the trigger to be disabled.

ALL

Indicates that all triggers defined at the scope of the ON clause are disabled.

Object_name

Is the name of the table or view on which the DML trigger trigger_name was created to execute.

Database

For a DDL trigger, indicates that trigger_name was created or modified to execute with database scope.

All Server

For a DDL trigger, indicates that trigger_name was created or modified to execute with server scope. ALL SERVER also applies to logon triggers.

Remarks

Triggers are enabled by default when they are created. Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger does not fire when any Transact-SQL statements on which it was programmed are executed. Triggers can be re-enabled by using ENABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.

Permissions

To disable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.

To disable a DDL trigger with server scope (ON ALL SERVER) or a logon trigger, a user must have CONTROL SERVER permission on the server. To disable a DDL trigger with database scope (ON DATABASE), at a minimum, a user must have ALTER ANY DATABASE DDL TRIGGER permission in the current database

Disable All Triggers In Data Base:

CREATE PROCEDURE [dbo].[DisableAllTriggers]

AS

DECLARE @string VARCHAR(8000)

DECLARE @tableName NVARCHAR(500)

DECLARE cur CURSOR

FOR SELECT name AS tbname FROM sysobjects WHERE id IN(SELECTparent_obj FROM sysobjects WHERE xtype='tr')

OPEN cur

FETCH next FROM cur INTO @tableName

WHILE @@fetch_status = 0

BEGIN

SET @string ='Alter table '+ @tableName + ' Disable trigger all'

EXEC (@string)

FETCH next FROM cur INTO @tableName

END

CLOSE cur

DEALLOCATE cur

GO

----To execute the SP

EXEC [DisableAllTriggers]

Disabling a Trigger for a Specific SQL Statement or Session:

Context_Info ( ):

Another way of accomplishing the task is to use the Context Info of the session. Context Info is a variable which belongs to the session. Its value can be changed using SET Context_Info

The trigger will mostly look like this:

USE AdventureWorks;

GO

-- creating the table in AdventureWorks database

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 TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE

AS

DECLARE @Cinfo VARBINARY(128)

SELECT @Cinfo = Context_Info()

IF @Cinfo = 0x55555

RETURN

PRINT 'Trigger Executed'

-- Actual code goes here

-- For simplicity, I did not include any code

GO

If you want to prevent the trigger from being executed you can do the following:

SET Context_Info 0x55555

INSERT dbo.Table1 VALUES(100)

Before issuing the INSERT statement, the context info is set to a value. In the trigger, we are first checking if the value of context info is the same as the value declared. If yes, the trigger will simply return without executing its code, otherwise the trigger will fire.

Using Temp table:

USE AdventureWorks;

GO

-- creating the table in AdventureWorks database

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 TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE

AS

IF OBJECT_ID('tempdb..#Disable') IS NOT NULL RETURN

PRINT 'Trigger Executed'

-- Actual code goes here

-- For simplicity, I did not include any code

GO

If you do not want the trigger to fire for a statement, let the trigger know by creating the the temporary table in your statement.

CREATE TABLE #Disable(ID INT)

-- Actual statement

INSERT dbo.Table1 VALUES(600)

DROP TABLE #Disable