How to Enable / Disable the triggers in SQL Server 2012?

Why we have to Enable / Disable a trigger? – Very simple, if you created some trigger for any table, and due to some region you don’t want to fire some of the trigger, so in place of delete, you can disable it and later as per requirement enable it.

Enabling a trigger does not re-create it. A disabled trigger still exists as an object in the current database, but does not fire. Enabling a trigger causes it to fire when any events on which it was originally programmed are executed.

Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can be disabled or enabled by using ALTER TABLE.

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

To enable a DDL or logon trigger with server scope (ON ALL SERVER), a user must have CONTROL SERVER permission on the server.

To enable 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.

-- Using Diabled command – DML Trigger

DISABLE TRIGGER dbo.MyTest1 ON dbo.NewContact;

--OR, use the ALTER TABLE DDL command with the ENABLE TRIGGER or DISABLE TRIGGER option:

ALTER TABLE dbo.Employee

ENABLE TRIGGER --or DISABLE TRIGGER

trgInsertMessage;

--Disabling a DDL trigger

DISABLE TRIGGER ProtectObjects ON DATABASE;

To view the enabled status of a trigger, use the OBJECTPROPERTY() function, passing to it the object ID of the trigger and the ExecIsTriggerDisabled option:

SELECT OBJECTPROPERTY(

       OBJECT_ID('dbo.trgInsertMessage'), 'ExecIsTriggerDisabled');

       -- o: enabled and 1: disabled

How to list all the Triggers?

The following query lists all the triggers in the database based on the sys.triggers catalog view. Contains a row for each object that is a trigger, with a type of TR or TA.

SELECT Sc.name + '.' + Ob.name as [table], Tr.name as [trigger],

       CASE (Tr.is_instead_of_trigger )

              WHEN 0 THEN 'after'

              WHEN 1 THEN 'instead of'

       END AS type,

       CASE (Tr.is_disabled)

              WHEN 0 THEN 'enabled'

              WHEN 1 THEN 'disabled'

       END AS status

FROM sys.triggers Tr

       JOIN sys.objects Ob

              ON Tr.parent_id = Ob.object_id

       JOIN sys.schemas Sc

              ON Ob.schema_id = Sc.schema_id

WHERE Tr.type = 'TR' and Tr.parent_class = 1

ORDER BY Sc.name + '.' + Ob.name, Tr.name

-- you can also use You can use syscomments and sysobjects to list the triggers

select xtype, object_Name(Parent_obj) Parent,

B.Name, text

from syscomments A

JOIN sysobjects B ON

A.id = B.id

Where xtype = 'TR'