How to Disable and Enable Triggers in SQL Server

Disable and Enable Triggers in SQL Server

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 for which it was programmed are executed.

Triggers can be re-enabled by using ENABLE TRIGGER. DML triggers defined on tables can also be disabled or enabled by using ALTER TABLE.

Purpose of the Following Trigger:-

Avoid dropping tables from the database

USE TestDB1;
GO

CREATE TRIGGER TRG_DB_SAFTY
ON DATABASE
FOR DROP_TABLE
AS
    RAISERROR('DROP IS NOT POSSIBLE. PLEASE CONTACT ADMIN.', 16, 1);
    ROLLBACK;

After creating the trigger, it can be disabled or enabled at any point in time.

Disable Trigger on Database

To disable (deactivate) a trigger, use the DISABLE keyword as follows:

USE TestDB1;
GO

DISABLE TRIGGER TRG_DB_SAFTY ON DATABASE;

Enable Trigger on Database

To enable a trigger on a database or another object, use the ENABLE keyword as follows:

USE TestDB1;
GO

ENABLE TRIGGER TRG_DB_SAFTY ON DATABASE;

Disabling All Triggers

Disable all server-scoped DDL triggers

USE TestDB1;
GO

DISABLE TRIGGER ALL ON ALL SERVER;

Disable All Triggers in the Database

USE TestDB1;
GO

DISABLE TRIGGER ALL ON DATABASE;
GO

Disable Triggers on a Table

USE TestDB1;
GO

DISABLE TRIGGER ALL ON dbo.MY_EMP;
GO