Script to Find Triggers and its Status in Database in SQL Server

To find list of triggers and its status in database in sql server

/****************Script / Query to List Triggers and its Status***********************/

Use databasename;

GO

SELECT

       TAB.name as Table_Name

     , TRIG.name as Trigger_Name

       , TRIG.create_date as Create_Date

     , TRIG.is_disabled

FROM [sys].[triggers] as TRIG

inner join sys.tables as TAB

on TRIG.parent_id = TAB.object_id

Below is Demo to find same, if you are new to sql server

/**************Create datbase***********************/

Create database dummy

GO

use Dummy

GO

/**************Create Table***********************/

CREATE TABLE [dbo].[emp](

[id] int PRIMARY KEY,

[name] VARCHAR(50)

)

GO

/**************Insert 1st record***********************/

INSERT INTO [dbo].[emp]  VALUES(1,'Saurabh')

GO

/**************Create trigger***********************/

CREATE TRIGGER test_Trigger ON [dbo].[emp]

instead OF INSERT AS

BEGIN

DECLARE @num INT

SELECT  @num=MAX(id) FROM [dbo].[emp]

INSERT INTO [dbo].[emp] (id,name) SELECT @num+1,inserted.name FROM inserted

END

/**************Check data***********************/

Select * from Emp

Now, Insert another row

INSERT INTO [dbo].[emp]  VALUES(1,'Saurabh')

Go

Select * from Emp

Now we will run our query to list triggers in database

Use databasename;

GO

SELECT

       TAB.name as Table_Name

     , TRIG.name as Trigger_Name

       , TRIG.create_date as Create_Date

     , TRIG.is_disabled

FROM [sys].[triggers] as TRIG

inner join sys.tables as TAB

on TRIG.parent_id = TAB.object_id