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

No comments:
Write comments

Please do not enter spam links

Services

More Services