Enable/Disable Triggers

I am going to explain different operations on Triggers(Enable/Disable/Get Status etc) with following example:

Suppose i create a Table named 'Employee_Test' with following schema

CREATE TABLE Employee_Test

(

Emp_ID INT Identity,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

And i create following Triggers on this table

CREATE TRIGGER Employee_Test_Insert ON [dbo].[Employee_Test]

FOR INSERT

AS

      PRINT 'INSERT trigger fired.'

GO

----------------------------------------------------------------

CREATE TRIGGER Employee_Test_Update ON [dbo].[Employee_Test]

FOR UPDATE

AS

      PRINT 'UPDATE trigger fired.'

GO

----------------------------------------------------------------

CREATE TRIGGER Employee_Test_Delete ON [dbo].[Employee_Test]

FOR DELETE

AS

      PRINT 'DELETE trigger fired.'

GO

I will use following t-sql commands to perform different operations on Triggers.

1. To Find All the Triggers created on a table with their status

-- Get All the Trigge's Created on a Table and their status

SELECT

    O.name  TriggerName, --Trigger Name

    T.name AS TableName, --Table On which Trigger is created

    (SELECT case when O.is_disabled = 0 then 'Enabled' Else 'Disabled' end)

    as Status --Staus of Trigger whether Enabled/Disabled

    FROM sys.triggers O

    INNER JOIN sys.tables T ON T.object_id = O.parent_id

    WHERE O.type = 'TR' AND T.name IN ('Employee_Test')

ORDER BY TableName

2. Find All the Trigger created in a database with their details

-- Get All the Triggers on database and their details

SELECT      Tables.Name TableName,

      Triggers.name TriggerName,

      Triggers.crdate TriggerCreatedDate,

      Comments.Text TriggerText

FROM      sysobjects Triggers

      Inner Join sysobjects Tables On Triggers.parent_obj = Tables.id

      Inner Join syscomments Comments On Triggers.id = Comments.id

WHERE      Triggers.xtype = 'TR'

      And Tables.xtype = 'U'

ORDER BY Tables.Name, Triggers.name

3. Show All the objects which are dependent on a given Trigger.

--show you all tables related to a trigger

sp_depends Employee_Test_Insert

4. Get Definition of a Trigger

--See the definition of the trigger

sp_helptext Employee_Test_Insert

5. Get All Triggers on a database

--See All the Triggers and their Tables

select name as 'Trigger Name', object_name(parent_id)as 'Table Name' from sys.triggers

                  --***OR***

-- get all MS SQL Server triggers.

select * from sysobjects where type = 'TR'

6. Enable a Trigger Created on a Table

--Enabling Trigger

ENABLE TRIGGER Employee_Test_Insert ON dbo.Employee_Test

                              --***OR***

ALTER TABLE dbo.Employee_Test ENABLE TRIGGER Employee_Test_Insert

7. Disable a Trigger Created on a Table

--Disabling Trigger

DISABLE TRIGGER Employee_Test_Insert ON dbo.Employee_Test

                              --***OR***

ALTER TABLE dbo.Employee_Test DISABLE TRIGGER Employee_Test_Insert

8. Disable All Triggers on a Table

--Disable All Triggers on table 'Employee_Test'

ALTER TABLE dbo.Employee_Test DISABLE TRIGGER ALL

9. Enable All Triggers on a Table

--Enable All Triggers on table 'Employee_Test'

ALTER TABLE dbo.Employee_Test ENABLE TRIGGER ALL