/****************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 commentsPlease do not enter spam links