Disable and enable triggers in SQL Server

To disable a trigger use this code:

USE Database_name
GO
DISABLE TRIGGER Trigger_Name ON Table_Name
GO

To enable a disabled trigger:

USE Database_name
GO
ENABLE TRIGGER Trigger_Name ON Table_Name
GO

You can enable and disable a trigger in SQL Server Management Studio by right-clicking on a trigger and selecting Enable or Disable:

You can run the following query to get a list of triggers that are disabled or enabled.
Disabled triggers:

USE Database_Name
GO
SELECT * FROM sys.triggers WHERE is_disabled = 1
GO

Enabled triggers:

USE Database_Name
GO
SELECT * FROM sys.triggers WHERE is_disabled = 0
GO

To see how to list all DML Triggers modified in last N days in a SQL Server Database:
List all DML Triggers modified in last N days in a SQL Server Database

To see how to list all DDL Triggers modified in last N days in a SQL Server Database:
List all DDL Triggers modified in last N days in a SQL Server Database

To see how to list all Triggers in a SQL Server Database:
How to list all Triggers in a SQL Server Database

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">