Create ,list and Alter the trigger associated with a table with SQL Server?

Hi friends,in this article i will explain about How to Create ,list and Alter the trigger associated with a table with SQL Server?.

I already explained in the previous articles about Binding and Sorting Grid in ASP.NET MVC using Jquery , JSON: Create Cascading DropDownList from Database using JQuery in MVC 4 Razor,How to Bind Data to DropDownList from Database using Stored Procedure in MVC 4 Razor and How to bind DropDownList from database in C# MVC 4 razor

Employee Table is as shown in below figure

CREATE Trigger on Employee Table

CREATE TRIGGER [dbo].[TR_Employee]    ON  [dbo].[Employee]    AFTER INSERT, UPDATE,DELETE AS BEGIN             SET NOCOUNT ON; PRINT 'TRIGGER OUTPUT: '+CONVERT(VARCHAR(50), @@ROWCOUNT) + ' rows were updated.' PRINT 'Table1 rows are Inserted' SELECT Emp_ID, Name, DepartmentName FROM inserted -- THIS IS TEMPORARY TABLE PRINT 'Table2 Rows are Deleted' SELECT Emp_ID, Name, DepartmentName FROM deleted -- THIS IS TEMPORARY TABLE END

Write insert command

INSERT INTO Employee (Name,DepartmentName,DOJ) VALUES('Sachin','SE',GETDATE())

Then the output is as shown in below figure.

Not allowing Delete on Table Employee

-------------------------------For Not Allowing Deletion---------------------------------- CREATE TRIGGER [dbo].[TR_Emp_Delete]             ON  [dbo].[Employee]     FOR DELETE AS BEGIN ROLLBACK PRINT 'Delete is not allowed for Employee Table.Contact your System administrator' END

Allowing deletion

--------For  Allowing Deletion---------- CREATE TRIGGER [dbo].[TR_Emp_Delete]             ON  [dbo].[Employee]     AFTER DELETE AS BEGIN             PRINT 'TRIGGER OUTPUT: '+CONVERT(VARCHAR(50), @@ROWCOUNT) + ' rows were updated.' END

To find the triggers in your Database

select * from sys.triggers

 List out all the triggers associated with the given table

Way 1:

select so.name, text from sysobjects so, syscomments sc where type = 'TR' and so.id = sc.id and text like '%YoutTableName%'

Way 2:

SELECT     TABLE_NAME      = so.name     ,trigger_name   = st.name     ,trigger_text   = sc.text     ,create_date    = st.create_date FROM sys.triggers st JOIN sysobjects so     ON st.parent_id = so.id JOIN syscomments sc     ON sc.id = st.[object_id] and text like '% YoutTableName %'

Way 3:

SELECT      TABLE_NAME     = so2.name     ,trigger_name   = so.name     ,trigger_text   = sc.text     ,create_date    = so.crdate FROM sysobjects so JOIN sysobjects so2     ON so.parent_obj = so2.id JOIN syscomments sc     ON sc.id = so.id WHERE so.TYPE = 'tr' and text like '% YoutTableName %'

Example:

select so.name, text from sysobjects so, syscomments sc where type = 'TR' and so.id = sc.id and text like '%Employee%'

The output of above query is as shown below.

"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook pageAspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog  for more articles."