Monday 3 March 2014

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?.
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." 

No comments:

Post a Comment

© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.